Come attribuire il fatturato ai fornitori

Benché il titolo di questo articolo suoni quasi come una provocazione, la sua stesura, come sempre, riflette dei compiti che in kubisco ci siamo trovati ad affrontare su richiesta dei nostri clienti e che riteniamo utile condividere con la community, usando dati di fantasia e generalizzando il problema.

Si supponga, in un’azienda commerciale che acquista e vende dei prodotti, di volere stimare quanto, della quantità venduta, sia attribuibile ad ogni fornitore.

Le ipotesi di lavoro sono:

1. ogni prodotto può essere acquistato da uno o più fornitori;
2. la stessa codifica di ogni prodotto è usata in acquisto e vendita;
3. esistono sia prodotti (codici) acquistati e non venduti sia prodotti (codici) venduti di cui non si ha traccia dell’acquisto.

Il modello dati è visibile in figura 1, insieme alle frecce indicanti i percorsi di propagazione dei filtri applicati alle dimensioni.

Com’è visibile in figura 1, il modello è molto semplice e consta di tre dimensioni (Fornitore, Articolo e Cliente) e due fatti (Acquisti e Vendite).

Con questo modello è semplice visionare, fornitore per fornitore, le quantità acquistate e, cliente per cliente, le quantità vendute di ogni codice (figure 2 e 3).

Si noti che il codice COD1 non ha vendite ma è presente negli acquisti e i codici COD5 e COD6 hanno vendite ma non sono presenti negli acquisti; gli altri codici sono stati, di conseguenza, sia comprati che venduti.

Sviluppo

La difficoltà nell’attribuire le quantità vendute ai fornitori risiede nel fatto che il modello non prevede un legame tra vendite e acquisti né in termini di relazioni (figura 1) né in termini di dati: nella tabella vendite (figura 4) non esiste, infatti, alcun riferimento al fornitore dei prodotti venduti (che tra l’altro potrebbe non essere unico).

Ciò implica la necessità, per ottenere una stima delle quantità vendute per fornitore, di decidere un criterio di assegnazione delle quantità vendute ai vari fornitori. La modalità scelta per questo articolo (non necessariamente la migliore né l’unica), è la seguente: per ogni codice che sia stato sia venduto che acquistato, la quota delle quantità vendute dello stesso codice assegnata ad un fornitore è pari alla quota percentuale dello stesso fornitore sull’acquisto dello stesso codice moltiplicata per le vendite totali dello stesso codice.

Il risultato che si vuole ottenere è mostrato in figura 5.

La prima cosa da chiarire è il totale di 17,00. Cosa rappresenta? Le quantità vendute sono pari a 55 (figura 3). Il numero 17 rappresenta le vendite dei soli codici acquistati; in altre parole, si tratta delle vendite dei soli codici che sono presenti (oltre che, ovviamente, nella tabella Vendite) nella tabella Acquisti. Perché questa restrizione? Perché se un codice venduto non risulta negli acquisti non c’è modo di attribuirne le vendite ad alcun fornitore e, d’altro canto, un codice non venduto non è parte delle vendite e, dunque, non necessita di alcuna attribuzione.

In figura 6 viene aggiunta, rispetto alla figura 3, la misura Qta Venduta Acquistata, che effettua la restrizione citata ed evidenzia il numero 17,00. I codici non acquistati, COD5 e COD6, ne sono, infatti, esclusi.

A seguire il codice DAX delle misure Qta Acquistata, Qta Venduta e Qta Venduta Acquistata, quest’ultima facente uso del concetto di tabella espansa nell’usare la tabella Acquisti come filtro di CALCULATE.

Qta Acquistata =
SUM ( Acquisti[qta] )

Qta Venduta =
SUM ( Vendite[qta] )

Qta Venduta Acquistata =

Tuttavia, se si prova a suddividere le vendite per fornitore, si ottiene un risultato non incoraggiante: la misura Qta Venduta restituisce il totale complessivo venduto in corrispondenza di ogni fornitore (figura 7). Osservando la figura 1, in cui è mostrato il modello dati, si nota che i filtri applicati alla tabella Fornitore, tramite una relazione 1 (1, lato tabella Fornitore) a molti (*, lato tabella Acquisti), si propagano alla tabella Acquisti ma non vanno oltre e, dunque, non raggiungono la tabella Vendite. I filtri, infatti, non si propagano dal lato molti (*) al lato 1 (1).

L’aggiunta, in figura 8, della misura Qta Venduta Acquistata migliora, a prima vista, il risultato (il totale è in effetti pari a 17,00 – si faccia riferimento alla figura 6 per confronto); tuttavia la misura non fornisce quanto richiesto, visto che i valori su ogni singolo fornitore rappresentano le quantità totali vendute per ogni codice acquistato dal fornitore stesso e, di conseguenza, la loro somma eccede il valore di 17,00.

Si prenda a riferimento, per rendersene conto, la figura 9. Il codice COD3, acquistato soltanto dal fornitore FORN1 in quantità pari a 40 e dal fornitore FORN3 in quantità pari a 5 e venduto in quantità pari a 5, mostra 5 come valore della misura Qta Venduta Acquistata in corrispondenza di entrambi i fornitori (in figura 9 la misura Qta Venduta è stata sostituita da Qta Acquistata per comodità di lettura).

In figura 9, la quantità venduta pari a 5, relativa al codice COD3, dovrebbe essere ripartita tra i fornitori FORN1 e FORN3 e così dovrebbe succedere per tutti gli altri codici venduti che siano anche stati acquistati (presenti, cioè, non solo nella tabella Vendite ma anche nella tabella Acquisti). Tale risultato non è ancora stato raggiunto.

Per raggiungere il risultato desiderato, è necessario implementare il criterio di attribuzione illustrato all’inizio dell’articolo. La quantità totale acquistata del codice COD3 è 45 (40 da FORN1 e 5 da FORN2) e così, seguendo il criterio adottato, la quota percentuale di quantità venduta del codice COD3 del fornitore FORN1 deve essere pari a 40/45 (88,9%) mentre quella del fornitore FORN2 pari a 5/45 (11,1%). Queste quote percentuali si vuole vengano applicate alle vendite totali del codice, pari a 5, ottenendo così il valore di quantità venduta di COD3 per il fornitore FORN1 come 5*0,889 = 4,44 e per il fornitore FORN2 come 5*0,111 = 0,56. Questi valori sono, in effetti, osservabili in figura 5.

Questo meccanismo è implementato nella misura Qta Venduta Fornitori, i cui risultati sono mostrati in figura 5 e il cui codice è a seguire.

Qta Venduta Fornitori =

La misura, per quanto a prima vista lunga e articolata è, in realtà, piuttosto semplice nel suo algoritmo. Come sempre, in DAX, è tutta una questione di tabelle. In effetti, il fulcro della misura è una tabella, memorizzata in una variabile dal nome acquistofornitoricodiciconvendita.

Attraverso DAX Studio (o creando in Power BI Desktop una tabella calcolata), è possibile visionare la tabella acquistofornitoricodiciconvendita per capirne la natura e apprezzarne l’assoluta semplicità (codice DAX a parte). A seguire l’output di DAX Studio (figura 10) in un filter context vuoto (dunque la tabella corrispondente al calcolo del totale in figura 5).

Il codice della query creata in DAX Studio è a seguire e semplicemente avvolge il codice della tabella in un EVALUATE (lo statement dichiarativo che deve essere presente in ogni query DAX) e al termine ordina per fornitore tramite una chiamata a ORDER BY.

EVALUATE (

In modo molto simile, si può visionare la tabella in corrispondenza di un filter context che includa il solo fornitore FORN1 (prima riga della figura 5): basterà iniettare con CALCULATETABLE il filtro sul fornitore (figura 11 e codice a seguire).

EVALUATE (

Infine, si può visionare la tabella in corrispondenza di un filter context che includa il solo fornitore FORN1 e il solo codice di prodotto COD3 (seconda riga della figura 5, si faccia riferimento alla figura 12 e al codice a seguire).

EVALUATE (

In sostanza, la tabella acquistofornitoricodiciconvendita genera, dopo avere raggruppato grazie a SUMMARIZE per fornitore e codice, quattro colonne calcolate: la quantità acquistata dal fornitore in riga del codice in riga (@acquistocodicefornitore), la quantità acquistata del codice in riga da tutti i fornitori (@acquistocodicetuttiifornitori), la vendita del codice in riga a tutti i clienti (@venditacodiceacquistato) e, infine, la quantità di vendita del codice in riga attribuita al fornitore in riga (@venditacodicefornitore) con la seguente espressione: @acquistocodicefornitore/@acquistocodicetuttiifornitori *@venditacodiceacquistato.

Conclusioni

I problemi, in DAX, sono quasi sempre problemi relativi alle tabelle. Quando un problema appare di difficile soluzione in DAX, bisogna smettere di scrivere codice e costruire visivamente (e a mano) la tabella che serve per risolvere il problema. Una volta chiara la tabella necessaria, troverete una o più funzioni DAX che vi permetteranno di costruirla. A quel punto, il 99% del problema è risolto. Lo ribadiamo con forza: il DAX non si può imparare a tentativi, bisogna studiarne le funzioni, applicarle, studiarle ancora, applicarle e così via, avendo chiari i concetti di filter context, row context, context transition, iteratori e tabelle espanse, cioè i pilastri del DAX.

Autore del Post