Il calcolo di un valore progressivo è piuttosto semplice in Excel, dove ogni cella di un foglio di lavoro può effettuare un calcolo differente ed è semplice ordinare le righe di una tabella. Tuttavia, in ambito di Analysis Services Tabular e dunque di Power Pivot per Excel, Power BI e SQL Server Data Tools, le cose si complicano un po’. Questa complessità aumenta ulteriormente nel caso in cui esistano elementi di pari valore e, di conseguenza, vada fatta una scelta di ordinamento ulteriore.
In questo articolo verrà fornito un esempio di soluzione usando l’ordinamento alfabetico degli elementi di pari valore, graze all’uso della funzione RANKX.
Sviluppo
Vediamo subito un esempio privo di elementi di pari valore, per poi passare al caso più complesso. Si consideri la tabella in figura 1, ClientiRidotta, che lista soltanto alcuni dei clienti presenti nella tabella Vendite, con in aggiunta due colonne calcolate che mostrano il margine medio percentuale delle transazioni e il totale delle vendite di ogni cliente.
Come si nota in figura 1, non esistono due clienti con lo stesso margine medio percentuale nella tabella ClientiRidotta. In figura 2 è visibile la tabella Vendite.
Il modello dati è visibile in figura 3, dove si nota un’ulteriore tabella, Clienti, in cui tutti i clienti sono listati. Quest’ultima tabella è visibile in figura 4 e mostra le stesse colonne calcolate già viste in figura 1 nella tabella ClientiRidotta.
Si noti, in figura 4, che nella tabella Clienti due coppie di clienti, C e B e G ed E, hanno lo stesso margine medio percentuale, al contrario di quanto succedeva in figura 1 che mostra la tabella ClientiRidotta.
Si supponga, dunque, di volere creare una colonna calcolata in ClientiRidotta che mostri il valore progressivo delle vendite, partendo dal cliente con margine medio percentuale più alto e proseguendo in maniera ordinata sulla base di q. Non esistendo, in ClientiRidotta, clienti con lo stesso margine medio percentuale, si può usare l’approccio classico in DAX: una coppia di iterazioni innestate della tabella. La colonna calcolata Progressivo Vendite Classico è visibile in figura 5 ed il relativo codice è mostrato a seguire.
RETURN
SUMX (
FILTER (
‘ClientiRidotta’,
‘ClientiRidotta’[Margine Medio] >= Margine
),
CALCULATE (
SUM ( Vendite[Ricavi] )
)
)
Le due iterazioni innestate sono rappresentate da quella generata da SUMX e, a seguire, quella generata da FILTER. Per precisione, si ricorda che a monte è presente un’ulteriore iterazione, automaticamente creata da Tabular durante la creazione di una colonna calcolata, che applicherà il codice sopra riportato su ogni riga della tabella.
Tuttavia, tale codice ha un comportamento anomalo nel caso in cui siano presenti clienti con lo stesso margine medio percentuale. In figura 6 si può notare tale comportamento, applicando lo stesso codice su una colonna calcolata generata sulla tabella Clienti.
Il problema è che, nel passare dal progressivo di 600 del cliente F a quello del cliente B, viene incluso nel computo anche il cliente C che ha lo stesso margine di B. In questo modo si passa di colpo da 600 a 900 mentre il risultato richiesto sarebbe 800 per B e 900 per C. Lo stesso problema si manifesta al progressivo di E che comprende anche G per lo stesso motivo, passando di colpo da 900 a 1.100.
Come risolvere? È necessario, per prima cosa, scegliere come comportarsi quando più clienti hanno lo stesso margine. Come esempio di soluzione, supponiamo di usare l’ordinamento alfabetico del nome del cliente. In altri termini, se due o più clienti hanno lo stesso margine medio percentuale, useremo il nome di ognuno, ordinato in senso alfabetico crescente, per decidere la sequenza di inclusione nel calcolo progressivo (con l’ipotesi, verificabile nelle figure 1 e 4, che non esistano clienti con lo stesso nome – in un caso reale il codice cliente, univoco per definizione, risolverebbe definitivamente questo ultimo dettaglio).
Il risultato, ancora relativo alla tabella Clienti, è osservabile in figura 7 nella colonna calcolata Progressivo Vendite Evoluto il cui codice è riportato a seguire.
VAR ClientiEMargini =
ALL (
Clienti[Clienti],
Clienti[Margine medio]
)
VAR RankingClienteMargineEsterno =
RANKX (
FILTER (
ClientiEMargini,
Clienti[Margine medio] = Margine
),
Clienti[Clienti]
)
RETURN
SUMX (
FILTER (
Clienti,
Clienti[Margine Medio] >= Margine
),
IF (
Clienti[Margine medio] > Margine,
CALCULATE (
SUM ( Vendite[Ricavi] )
),
VAR RankingClienteMargineInterno =
RANKX (
FILTER (
ClientiEMargini,
Clienti[Margine medio] = Margine
),
Clienti[Clienti]
)
RETURN
IF (
RankingClienteMargineInterno >= RankingClienteMargineEsterno,
CALCULATE (
SUM ( Vendite[Ricavi] )
)
)
)
)
A seguire lo stesso codice sopra riportato, commentato per i meno esperti.
Progressivo Vendite Evoluto =— del cliente correntemente iterato (iterazione colonna calcolata,
— cui ci si riferirà come Esterna)
VAR Margine = Clienti[Margine medio] — Nella variabile ClientiEMargini conservo l’elenco di tutte
— le combinazioni di cliente e margine medio percentuale
VAR ClientiEMargini =
ALL ( Clienti[Clienti], Clienti[Margine medio] ) — Nella variabile RankingClienteMargineEsterno conservo la posizione
— alfabetica del nome del cliente correntemente iterato rispetto a quello
— di tutti i clienti con lo stesso margine medio percentuale
VAR RankingClienteMargineEsterno =
RANKX (
FILTER ( ClientiEMargini, Clienti[Margine medio] = Margine ),
Clienti[Clienti]
)
RETURN
— Itero la tabella Clienti, limitata ai clienti che hanno margine medio
— percentuale maggiore o uguale a quello del cliente correntemente iterato
— dall’iterazione esterna (questa iterazione è innestata dentro quella
— creata dalla colonna calcolata stessa e detta Esterna, la indicheremo
— come Interna). Lo faccio tramite SUMX per sommare tutti gli importi di
— vendita dei clienti con margine maggiore di quello del cliente correntemente
— iterato dall’iterazione Esterna, e di quelli con margine uguale ma ordine
— alfabetico del nome prioritario
SUMX (
FILTER ( Clienti, Clienti[Margine Medio] >= Margine ),
IF (
Clienti[Margine medio] > Margine,
— se il margine medio percentuale del
— cliente iterato internamente è maggiore di quello iterato esternamente
— sommo le vendite relative senza curarmi di altro (eventuali altri clienti
— con lo stesso margine possono essere sommati, essendo il margine strettamente
— superiore a quello che cliente iterato esternamente)
CALCULATE (
SUM ( Vendite[Ricavi] )
),
— se, invece, il margine medio percentuale del cliente iterato internamente è
— uguale a quello iterato esternamente, stabilisco la posizione, in termini di
— ordine alfabetico, del nome del cliente itetrato internamente rispetto a quello
— di tutti gli altri clienti con lo stesso margine
VAR RankingClienteMargineInterno =
RANKX (
FILTER ( ClientiEMargini, Clienti[Margine medio] = Margine ),
Clienti[Clienti]
)
RETURN
— e sommo, infine, solo le vendite dei clienti iterati internamente con lo stesso
— margine di quello iterato esternamente ma con ordine alfabetico del nome prioritario
IF (
RankingClienteMargineInterno >= RankingClienteMargineEsterno,
CALCULATE ( SUM ( Vendite[Ricavi] ) )
)
)
)
La chiave della soluzione è l’uso della funzione RANKX, un iteratore molto potente che stabilisce il ranking di un’espressione all’interno di una tabella.
Conclusioni
È possibile potenziare lo schema classico del calcolo di un progressivo in DAX tramite la funzione RANKX. Nell’usare RANKX, un iteratore, è molto importante considerare il filter context (vuoto in questo caso, trattandosi di un ‘applicazione ad una colonna calcolata, ma in generale non vuoto se si usa RANKX in una misura) e la context transition eventualmente introdotta da CALCULATE.
Autore del Post
