Filtro sulle misure, questo sconosciuto

La possibilità di applicare filtri ad una tabella pivot risale alle origini delle tabelle pivot stesse. Riguardo Excel, questa funzionalità ha sempre, tuttavia, riguardato esclusivamente filtri applicati a colonne. In figura 1 è mostrata una pivot in Excel che mostra due calcoli: le vendite (SommadiSalesAmount) e il prezzo medio (SommadiPrezzoMedio), quest’ultimo essendo un campo calcolato. I due calcoli sono suddivisi per territorio (SalesTerritoryKey). Un valore di SalesTerritoryKey, uno di Sales[CurrencyKey] e due di Sales[TransType] sono stati esclusi tramite filtri. Il report ha, dunque, un totale di tre filtri applicati.

Questo tipo di filtraggio appare del tutto naturale. Le colonne, infatti, contengono liste di valori. È, dunque, piuttosto ragionevole l’idea di potere filtrare queste liste.

Non è, però, possibile nelle tabelle pivot di Excel applicare filtri a campi calcolati, si osservi in figura 2 il messaggio di errore che si ottiene se si prova a posizionare il campo calcolato PrezzoMedio nella zona Filtri per, per esempio, limitare il report ai territori con un prezzo medio superiore a 100 €.

I campi calcolati, in effetti, non contengono liste di valori bensì formule, dunque come potrebbe mai un filtro essere applicato in assenza di una lista di valori da filtrare?

Power BI Desktop, cioè Analysis Services Tabular, tuttavia, permette di farlo. In figura 3 lo stesso report della figura 2 realizzato in Power BI Desktop con i tre filtri su colonne replicati.

In figura 4, lo stesso report della figura 3 con, in aggiunta, un filtro applicato alla misura Prezzo Medio (che ammette soltanto i valori oltre 100).

Il risultato del filtraggio è quello che ci si sarebbe aspettati: la riga relativa a SalesTerritory pari a 1 è sparita, visto che il relativo prezzo medio era pari a 50.

Scopo di questo articolo è spiegare come funziona il filtraggio applicato ad una misura (non è semplice, infatti, come può apparire in figura 4) e, successivamente, illustrare e  spiegare un risultato controintuitivo in cui ci si può imbattere usando il filtraggio delle misure in Power BI Desktop. Infine, sarà mostrato il risultato ed il codice di un Calculation Group che risolverà il puzzle. Nel file .pbix scaricabile è inclusa la soluzione al puzzle con anche un primo tentativo di una soluzione differente, poi abbandonato per ragioni di efficienza.

In figura 5 è mostrato il modello dati Power BI usato, un classico snowflake. In figura, sono mostrate anche tabelle aggiuntive, scollegate dal modello, il cui scopo sarà illustrato più avanti nell’articolo.

Sviluppo

Si osservi il report Power BI Desktop in figura 6, basato ancora sul modello in figura 5, in cui è mostrata una classificazione ABC dinamica.

Il report mostra, per ogni sottocategoria di prodotto, quattro misure: le vendite (Sales), le vendite cumulate (Cumulated ALLSEL SubCategory Sales), le vendite cumulate percentuali rispetto al totale delle vendite (Cumulated SubCategory Sales Pct ALLSEL) e, infine, la classificazione ABC (ABC ALLSEL). La sigla ALLSEL è inserita nei nomi delle misure per evidenziare che in esse è stato fatto uso della funzione ALLSELECTED. Alcune sottocategorie di prodotto sono state, infatti, escluse dal report attraverso lo slicer sulla colonna Customer[EnglishProductSubCategoryName] nel riquadro in alto al centro in figura 6. La classificazione ABC dinamica avviene sulla base delle soglie definite dai due slicer nel riquadro in alto a sinistra, sempre in figura 6. Fino a quando i valori cumulati sono inferiori o uguali al 65% delle vendite, la categoria assegnata è A, oltre si passa a B fino a valori minori o uguali a 95%, ancora oltre si passa a C.

La misura Sales ha il seguente codice:

Si supponga adesso, di volere mostrare soltanto le righe relative alla categoria B in figura 6. Si può applicare un filtro alla misura ABC ALLSEL, chiedendo di mostrare soltanto i valori pari a B (in figura 6 le sole sottocategorie Touring Bikes, Tires and Tubes e Helmets). Il risultato osservabile in figura 7, tuttavia, non è quello desiderato.

Si notano quattro apparenti anomalie in figura 7:

1. oltre alle tre righe attese, è presente la riga Road Bikes, che era classificata in figura 6 come A (e risulta classificata in figura 7 come B);
2. le tre righe attese sono presenti, ma in due casi su tre (Tires and Tubes e Helmets) si osserva una classificazione diversa in figura 7 da quella mostrata in figura 6 (C invece di B);
3. la classificazione di Tires and Tubes e Helmets, dunque, non è in linea con il filtraggio imposto (risulta in figura 7 C invece di B);
4. i valori delle vendite di Touring Bikes e Road Bikes sono differenti in figura 7 rispetto a quelli in figura 6.

Per chiarire le apparenti anomalie e ottenere quanto voluto, cioè le sole righe relative a Touring Bikes, Tires and Tubes e Helmets, è indispensabile comprendere bene come avviene l’applicazione del filtro sulla misura ABC ALLSEL.

L’applicazione del filtro, per prima cosa, non avviene sulla colonna della matrice che mostra la misura stessa. Ne è prova il fatto che la misura può essere rimossa dal report e il filtro continua ad avere effetto, come è visibile in figura 8.

Dunque, come avviene il filtraggio? La risposta è che esso avviene alla granularità stabilita dalla serie di colonne messe in Righe in figura 6, evidenziata nel riquadro in alto a destra nella stessa figura (le colonne in Righe sono ProductSubCategory[EnglishProductSubcategoryName] e Customer[EnglishOccupation]). Eventuali colonne presenti in Colonne, al contrario, non hanno rilevanza sulla granularità dell’applicazione del filtraggio.

Il filtro viene, dunque, applicato – in questo particolare caso –  ad ogni coppia sottocategoria di prodotto/occupazione del cliente (ProductSubCategory[EnglishProductSubcategoryName]/ Customer[EnglishOccupation]). In corrispondenza di ognuna di esse, la misura Prezzo Medio viene calcolata e la relativa riga viene visualizzata soltanto se il valore di Prezzo Medio calcolato rientra nel range impostato dal filtro sulla misura. Questo importante dettaglio fornisce alcune risposte alle quattro apparenti anomalie in figura 7, in particolare al riguardo delle 1, 3 e 4.

Se, infatti, a partire dalla figura 6, si espande tutta la gerarchia, si nota che la sottocategoria Road Bikes aveva, in effetti, una riga catalogata come B, seppur non visibile senza l’espansione stessa: quella relativa ai clienti Professional. Il valore di 832.091 visibile in figura 7 è proprio quello di questa riga. Per Touring Bikes, quattro righe erano classificate come B, la somma delle vendite relative corrisponde al valore 1.362.268 mostrato in figura 7. Per le sottocategorie Tires and Tubes e Helmets, invece, il valore di classificazione non cambiava perché ogni relativa riga di occupazione dei clienti era già catalogata come B. La figura 9 mostra i dettagli sopra riportati per Road Bikes e Touring Bikes. Si precisa che essa mostra il report  nelle condizioni delle figura 6, in cui cioè il filtro sulla misura non è presente, con la gerarchia in riga espansa.

Resta l’apparente anomalia 2 in figura 7. Perché il valore di classificazione cambia dopo l’applicazione del filtro alla misura di classificazione? La risposta è proprio che il filtro non avviene sui valori della misura mostrati nel report, dunque se si inserisce nella matrice la stessa misura su cui si applica il filtro, essa si adatta al contesto già filtrato dal filtro applicato alla misura stessa. In effetti, i valori di classificazione visibili in figura 7 sono in linea con i limiti delle categorie A, B e C.

Se si vuole ottenere, a partire dalla figura 6, la possibilità di filtrare le sole tre righe delle sottocategorie Touring Bikes, Tires and Tubes e Helmets imponendo B come classificazione, la granularità di applicazione del filtro sulla misura deve essere al livello della sottocategoria di prodotto. In figura 10 è mostrato il risultato dell’applicazione del filtro una volta che la colonna Customer[English Occupation] è stata rimossa da Righe.

Le righe filtrate sono adesso quelle attese. Resta la differente classificazione. Rimuovendo la misura filtrata dal report, tuttavia, si ottiene quanto richiesto, in figura 11, a parte il valore cumulato delle vendite che è ancora adattato al report (e porterebbe alla classificazione modificata se la misura ALLSEL fosse presente nel report).

Anche accettando questa imperfezione, come può adesso essere dedotta la classificazione delle righe filtrate (come farebbe un utente con cui venisse condiviso questo report a capire che le tre sottocategorie visibili in figura 11 sonno classificate come B)? È piuttosto scomodo dovere mantenere aperto il pannello laterale dei filtri o doverlo aprire per ricordarsi cosa è filtrato. Sarebbe altresì scomodo, dispendioso e inefficiente – sia in termini di spazio occupato nel canvas di Power BI che di manutenzione del codice – creare tre misure, A Selected Subcategory, B Selected Subcategory e C Selected Subcategory per mostrare nel nome stesso delle misure le rispettive categorie e permettere di dedurre così il valore filtrato. Questa soluzione è soltanto abbozzata nel pbix allegato, come accennato ad inizio articolo, e ne verrà dato qualche ulteriore dettaglio più avanti nell’articolo.

La soluzione dovrebbe permettere, dunque, non solo di decidere quali valori tra A, B e C mostrare nel report attraverso uno slicer visibile e selezionabile ma anche di osservare i valori A, B e C nel report. Infine, questi valori dovrebbero essere coerenti col filtraggio imposto. Questo implica di non applicare filtri alla misura ALLSEL (essa si adatterebbe al contesto, come già mostrato). Inoltre, appare subito strategico ragionare in termini di Calculation Group in modo da non dovere replicare il codice, se si dovesse decidere di mostrare nel report una misura diversa da Sales. Infine, si dovrà ricordare che la misura scelta potrebbe non essere additiva come Sales (per esempio il numero distinto dei clienti a cui si è venduto, Active Customers), dunque il codice non potrà usare, per esempio, una SUMX per i valori da calcolare al Totale bensì usare il filter context appropriato.

Il risultato desiderato è mostrato in figura 12 dove, si noti, non è applicato alcun filtro alla misura Sales. In effetti, in figura 12, è presente soltanto una misura, Sales, in Valori. I cinque calcoli mostrati sono il risultato dell’applicazione di un Calculation Group alla misura, dotato di cinque Calculation Item. Nel calcolo cumulato percentuale sono adesso mostrate due cifre decimali, ma i valori sono del tutto identici a quelli di figura 6. Il Calculation Group in Tabular si presenta come una tabella dotata di una sola colonna, i cui elementi sono i cosiddetti Calculation Item, che descrivono una variante di una misura e possono essere applicati a qualunque misura. La lista dei Calculation Item è visibile nello slicer del riquadro a sinistra in figura 12. Il Calculation Group si chiama Dynamic ABC Measure Filter Improved per distinguerlo dalla prima soluzione solo parzialmente implementata, in quanto non efficiente (crea le tre già citate misure, A Selected Subcategory, B Selected Subcategory e C Selected Subcategory) rappresentata dal Calculation Group dal nome Dynamic ABC Measure Filter. Qualche dettaglio in più su questa soluzione è riportato nel terzo video della playlist sui filtri applicati alle misure.

In figura 13 lo stesso report con una misura non additiva, Active Customers, cioè il numero di clienti che hanno fatto almeno un acquisto nel contesto del report. Il report funziona correttamente. Il codice di Active Customers è il seguente.

Il Calculation Group Dynamic ABC Measure Filter Improved, come già accennato, annovera cinque Calculation Item, listati e descritti qui di seguito, e tiene conto della selezione sullo slicer in alto al centro in figura 12 e 13, attraverso cui l’utente decide quali valori tra A, B e C mostrare. Gli stessi valori sono visibili nel report e coerenti con la selezione. Lo slicer evidenziato in alto accanto a sinistra, come già in precedenza indicato, seleziona le sottocategorie di interesse.

Lo slicer in alto al centro seleziona i valori A, B e C da una tabella calcolata, ABC Values, dotata di un’unica colonna, ABC Values[ABC]. Questa è una delle sei tabelle aggiuntive a cui si era fatto breve cenno ad inizio articolo (figura 5). Il codice della tabella calcolata ABC Values è il seguente.

Le altre cinque tabelle aggiuntive sono:

1. la tabella del Calculation Group Dynamic ABC Measure Filter Improved;
2. la tabella del Calculation Group Dynamic ABC Measure Filter;
3. la tabella di un Calculation Group aggiuntivo per l’ordinamento dei valori in matrice, dal nome Dynamic ABC Measure Sorting, che sarà descritto più avanti nell’articolo;
4. una tabella di un singola colonna per la selezione dei valori limite della categoria A;
5. una tabella di un singola colonna per la selezione dei valori limite della categoria B.

I Calculation Item di Dynamic ABC Measure Filter Improved sono:

• Value With Filtered Total: calcola il valore della misura e, nel totale, considera le sole sottocategorie visibili, sulla base della selezione sugli slicer per i valori A, B e C e per le sottocategorie di prodotto;
• Value With Unfiltered Total: calcola il valore della misura e, nel totale, considera tutte le sottocategorie (visibili e non), sulla base della selezione sugli slicer per i valori A, B e C e per le sottocategorie di prodotto – questo è il valore su cui devono essere calcolati i valori cumulati percentuali;
• Cumulated Value By Selected Subcategory: calcola il valore cumulato della misura;
• Cumulated Value By Selected Subcategory Pct: calcola il valore cumulato della misura in percentuale al totale di tutte le sottocategorie (visibili e non), sulla base della selezione sullo slicer per le sottocategorie di prodotto;
• ABC Selected Subcategory: calcola la classificazione A, B o C sulla base del valore cumulato della misura in percentuale.

Ecco il codice di ognuno dei Calculation Item di Dynamic ABC Measure Filter Improved.

Value With Filtered Total

Value With Unfiltered Total

Cumulated Value By Selected Subcategory

Cumulated Value By Selected Subcategory Pct

ABC Selected Subcategory

Un ulteriore Calculation Group, Dynamic ABC Measure Sorting, si occupa di gestire l’ordinamento dei valori, permettendo di basarlo sul valore alfabetico della colonna in Righe o sulla base dei valori della misura. La necessità di questo Calculation Group è molto tecnica ed esula dallo scopo di questo articolo, tuttavia a seguire ne sono mostrati i Calculation Item col relativo codice.

Alphabetical

Value

Conclusioni

Attraverso i Calculation Group si possono generare report che risolvono problematiche relative al filtraggio applicato alle misure, caratteristica molto potente di Power BI, che può tuttavia generare risultati inattesi se non se ne conosce bene il meccanismo di funzionamento. Lo sforzo di creazione dei Calculation Group viene più che compensato dalla loro flessibilità, che permette di usare qualunque misura. Bisogna sempre considerare che la misura su cui un Calculation Group sarà applicato potrebbe non essere additiva, dunque va considerato il filter context corretto e non va usata una tecnica di aggregazione dei subtotali (ad esempio SUMX), altrimenti il campo di applicabilità del Calculation Group risulterà limitato. Infine, per una caratteristica dei Calculation Group non approfondita in questo articolo, è necessario un semplice Calculation Group addizionale per gestire l’ordinamento dei valori mostrati nel report.

Autore del Post

Francesco Bergamaschi e David Bianconi

See author's posts