Quando usare colonne e campi calcolati

Quando imparano a usare per la prima volta Power Pivot, molti utenti scoprono la reale potenza che risiede nell'aggregazione o nel calcolo di un risultato in un certo modo. Se i dati contengono una colonna con valori numerici, è possibile aggregarla facilmente selezionandola in una tabella pivot o in un elenco campi Power View. Per la sua natura numerica, verrà automaticamente sommata, conteggiata o ne verrà calcolata la media, o verrà eseguito qualsiasi tipo di aggregazione selezionato. Questa caratteristica è nota come misura implicita. Le misure implicite sono un ottimo metodo per un'aggregazione semplice e rapida, ma hanno limiti che possono quasi sempre essere superati con esplicite misure e colonne calcolate.

Si osserverà prima di tutto un esempio in cui viene usata una colonna calcolata per aggiungere un nuovo valore di testo a ogni riga in una tabella denominata Product. Ogni riga nella tabella Product contiene tutti i tipi di informazioni relative a ogni prodotto da vendere. Sono disponibili colonne per nome prodotto, colore, dimensioni, prezzo del rivenditore e così via. C'è poi un'altra tabella correlata, denominata Product Category, che contiene una colonna ProductCategoryName. L'obiettivo è che ogni prodotto nella tabella Product includa il nome della categoria del prodotto contenuto nella tabella Product Category. Nella tabella Product è possibile creare una colonna calcolata denominata Product Category simile alla seguente:

La nuova formula Categoria prodotto usa la funzione DAX CORRELATA per ottenere i valori dalla colonna ProductCategoryName nella tabella Categoria prodotto correlata e quindi immette tali valori per ogni prodotto (ogni riga) nella tabella Product.

Questo è un ottimo esempio di come sia possibile usare una colonna calcolata per aggiungere un valore fisso per ogni riga che sarà possibile usare in un secondo momento nell'area ROWS, COLUMNS o FILTERS della tabella pivot o in un report Power View.

Verrà ora creato un altro esempio, in cui si vuole calcolare un margine di profitto per le categorie di prodotti. Si tratta di uno scenario comune, anche in molte esercitazioni. La tabella Sales nel modello di dati contiene dati delle transazioni ed esiste una relazione tra la tabella Sales e la tabella Product Category. Nella tabella Sales è presente una colonna contenente gli importi di vendita e un'altra colonna che contiene i costi.

È possibile creare una colonna calcolata che calcoli un importo di profitto per ogni riga, sottraendo i valori nella colonna COGS dai valori della colonna SalesAmount, in questo modo:

A questo punto è possibile creare una tabella pivot e trascinare il campo Product Category nell'area COLUMNS e il nuovo campo Profit nell'area VALUES (una colonna in una tabella in PowerPivot è un campo nell'elenco dei campi di una tabella pivot). Il risultato è una misura implicita denominata Sum of Profit. È un importo aggregato dei valori della colonna di profitto per ognuna delle categorie di prodotti diverse. Il risultato avrà l'aspetto seguente:

In questo caso, il profitto può essere usato solo come campo nell'area VALUES. Se si inserisce Profit nell'area COLUMNS, la tabella pivot sarà simile alla seguente:

Il campo Profit non fornisce alcuna informazione utile quando viene inserito nelle aree COLUMNS, ROWS o FILTERS. È utile solo come valore aggregato nell'area VALUES.

È stata creata una colonna denominata Profit che calcola un margine di profitto per ogni riga nella tabella Sales. È stata quindi aggiunta la colonna Profit all'area VALUES della tabella pivot, creando automaticamente una misura implicita, in cui viene calcolato un risultato per ciascuna categoria di prodotto. Se si ritiene che in realtà sia stato calcolato due volte il profitto per le categorie di prodotto, è esatto. È stato prima calcolato il profitto per ogni riga della tabella Sales, quindi aggiunta la colonna Profit all'area VALUES, dove è stata aggregata per ciascuna delle categorie di prodotto. Se si ritiene quindi che non era poi così necessario creare la colonna calcolata Profit, anche questo è vero. Ma non sarebbe stato possibile calcolare il profitto senza prima aver creato una colonna calcolata Profit.

Il profitto andrebbe meglio calcolato come misura esplicita.

Per il momento lasceremo la colonna calcolata Profit nella tabella Sales, Product Category in COLUMNS e Profit in VALUES della tabella pivot, per confrontare i risultati.

Nell'area di calcolo della tabella Sales, verrà creata una misura denominata Total Profit(per evitare conflitti di nomi). Alla fine, produrrà gli stessi risultati ottenuti in precedenza, ma senza la colonna calcolata Profit.

Prima di tutto, nella tabella Sales, selezionare la colonna SalesAmount e quindi fare clic su Somma automatica per creare una misura esplicita Sum of SalesAmount. Tenere presente che una misura esplicita viene creata nell'area di calcolo di una tabella in Power Pivot. Verrà eseguita la stessa operazione per la colonna COGS. Verranno rinominate Total SalesAmount e Total COGS per poterle identificare più facilmente.

Quindi viene creata un'altra misura con questa formula:

Total Profit:=[ Total SalesAmount] - [Total COGS]

Dopo aver modificato il formato della nuova misura Total Profit in valuta, sarà possibile aggiungerla alla tabella pivot.

Come si può vedere, la nuova misura Total Profit restituisce gli stessi risultati della creazione di una colonna calcolata Profit e il successivo inserimento della stessa in VALUES. La differenza è che la misura Total Profit è molto più efficiente e rende il modello di dati più snello e pulito, perché il calcolo viene eseguito al momento e solo per i campi selezionati per la tabella pivot. Dopo tutto, la colonna calcolata Profit non serve davvero.

Perché è importante quest'ultima parte? Le colonne calcolate aggiungono dati al modello di dati, e i dati occupano spazio in memoria. Se si aggiorna il modello di dati, le risorse di elaborazione dovranno ricalcolare anche tutti i valori nella colonna Profit. Non è davvero necessario occupare risorse in questo modo perché l'obiettivo è calcolare il profitto quando si selezionano i campi per cui si vuole conoscere il profitto nella tabella pivot, come categorie di prodotti, area geografica o date.

Ecco un altro esempio. Una colonna calcolata crea risultati che a un primo sguardo sembrano corretti, ma...

In questo esempio si vogliono calcolare gli importi di vendita come percentuale delle vendite totali. Verrà creata una colonna calcolata denominata % of Sales nella tabella Sales, in questo modo:

La formula indica: Per ogni riga nella tabella Sales, dividere l'importo nella colonna SalesAmount per la SOMMA totale di tutti gli importi nella colonna SalesAmount.

Se si crea una tabella pivot e si aggiunge Product Category nell'area COLUMNS e si seleziona la nuova colonna % of Sales da inserire nell'area VALUES, si ottiene la somma totale delle percentuali di vendita per ciascuna delle categorie di prodotti.

Ok. Fin qui tutto bene. Tuttavia, è possibile aggiungere un filtro dei dati. Aggiungiamo Calendar Year e quindi selezioniamo un anno. In questo caso, selezioniamo il 2007. Ecco il risultato ottenuto.

A prima vista, potrebbe comunque sembrare corretto. Tuttavia, le percentuali dovrebbero in realtà ammontare a un totale del 100%, perché si vuole conoscere la percentuale delle vendite totali per ciascuna categoria di prodotti per l'anno 2007. Quindi qual è la causa del problema?

La colonna % of Sales ha calcolato una percentuale per ciascuna riga che è il valore nella colonna SalesAmount diviso per la somma totale di tutti i valori nella colonna SalesAmount. I valori in una colonna calcolata vengono corretti. Si tratta di un risultato non modificabile per ogni riga della tabella. Quando si è aggiunta la colonna % of Sales alla tabella pivot era aggregata come somma di tutti i valori nella colonna SalesAmount. La somma di tutti i valori nella colonna % of Sales sarà sempre 100%.

È possibile eliminare la colonna calcolata % of Sales perché non è di alcun aiuto. Verrà invece creata una misura che calcola correttamente la percentuale di vendite totali, indipendentemente da qualsiasi filtro o filtro dei dati applicato.

Ricordare la misura TotalSalesAmount creata in precedenza, quella che somma semplicemente la colonna SalesAmount? È stato usato come argomento nella misura Total Profit e lo useremo di nuovo come argomento nel nuovo campo calcolato.

Viene creata una nuova misura con la formula seguente:

% of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

La formula indica: Dividere il risultato di Total SalesAmount per la somma totale di SalesAmount senza alcun filtro di colonna o riga diverso da quelli definiti nella tabella pivot.

A questo punto, se si aggiunge la nuova misura % of Total Sales alla tabella pivot, si otterrà:

Così va meglio. Ora la misura % of Total Sales per ogni categoria di prodotto viene calcolata come percentuale delle vendite totali per l'anno 2007. Se si seleziona un altro anno oppure più di un anno nel filtro dei dati CalendarYear, si ottengono nuove percentuali per le categorie di prodotti, ma il totale complessivo è comunque 100%. È possibile aggiungere anche altri filtri dei dati e filtri. La misura % of Total Sales produrrà sempre una percentuale delle vendite totali indipendentemente dai filtri o dai filtri dei dati applicati. Con le misure, il risultato sarà sempre calcolato in base al contesto determinato dai campi nelle aree COLUMNS e ROWS e in base a eventuali filtri o filtri dei dati applicati. Ecco la vera potenza delle misure.

Ecco alcune indicazioni per decidere se una colonna calcolata o una misura è appropriata per un'esigenza di calcolo specifica:

Usare le colonne calcolate

• Se si vuole che i nuovi dati vengano visualizzati in RIGHE, COLONNE o IN FILTRI in una tabella pivot o in un ASSE, LEGENDA o, AFFIANCA PER in una visualizzazione Power View, è necessario usare una colonna calcolata. Proprio come le normali colonne di dati, le colonne calcolate possono essere usate come campo in qualsiasi area, e se sono numeriche possono essere aggregate anche nell'area VALUES.

• Se si vuole che i nuovi dati siano un valore fisso per la riga. Ad esempio, si ha una tabella date con una colonna di date e si vuole aggiungere una nuova colonna che contenga solo il numero del mese. È possibile creare una colonna calcolata che calcoli solo il numero del mese in base alle date nella colonna Data. Ad esempio, = MONTH('Date'[Date]).

• Se si vuole aggiungere un valore di testo per ogni riga a una tabella, usare una colonna calcolata. I campi con valori di testo non possono essere mai aggregati nell'area VALUES. Ad esempio, =FORMAT('Date'[Date],"mmmm") indica il nome del mese per ogni data nella colonna Data nella tabella Data.

Usare le misure

• Se il risultato del calcolo dipende sempre da altri campi selezionati in una tabella pivot.

• Se occorre eseguire calcoli più complessi, ad esempio calcolare un conteggio basato su un filtro di qualche tipo, o calcolare un confronto anno dopo anno o una variazione, usare un campo calcolato.

• Se si vogliono mantenere al minimo le dimensioni della cartella di lavoro e ottimizzarne le prestazioni, creare un numero illimitato di calcoli come misure. In molti casi, tutti i calcoli possono essere misure, riducendo in modo significativo le dimensioni della cartella di lavoro e accelerando i tempi di aggiornamento.

Si tenga presente che non c'è nulla di sbagliato nel creare colonne calcolate (come ad esempio la colonna Profit) e quindi aggregarle in una tabella pivot o un report. È in realtà un modo semplice per acquisire informazioni e creare calcoli personalizzati. Con l'aumentare della comprensione di queste due potenti funzionalità di Power Pivot, si vorrà creare il modello di dati più efficiente e preciso possibile. Ci si auspica che quanto appreso in questa sede possa essere d'aiuto. Sono anche disponibili altre ottime risorse che possono assistere in questa operazione. Eccone alcune: Contesto nelle formule DAX, Aggregazioni in Power Pivot e Centro risorse DAX. Inoltre, anche se è un po' più avanzato e rivolto ai professionisti della contabilità e delle finanze, la modellazione e l'analisi dei dati di profitto e perdita con Microsoft Power Pivot nell'esempio Excel viene caricata con esempi di modelli di dati ed esempi di formule.