Le aggregazioni consentono di comprimere, riepilogare o raggruppare i dati. Quando si inizia con dati non elaborati da tabelle o altre origini dati, i dati sono spesso piatti, ovvero molti dettagli, ma non sono stati organizzati o raggruppati in alcun modo. Questa mancanza di riepiloghi o struttura potrebbe rendere difficile individuare modelli nei dati. Una parte importante della modellazione dei dati consiste nel definire aggregazioni che semplificano, astraggono o riepilogano i modelli in risposta a una specifica domanda aziendale.
Le aggregazioni più comuni, ad esempio quelle che usano MEDIA,CONTEGGIO, DISTINCTCOUNT,MAX,MIN o SOMMA , possono essere create automaticamente in una misura usando Somma automatica. Altri tipi di aggregazioni, ad esempio MEDIA.X, CONTA.XX, CONTA.RIGA o SOMMA.X restituiscono una tabella e richiedono una formula creata usando DAX (Data Analysis Expressions).
Informazioni sulle aggregazioni in Power Pivot
Scelta dei gruppi per l'aggregazione
Quando si aggregano i dati, si raggruppano i dati in base ad attributi quali prodotto, prezzo, area geografica o data e quindi si definisce una formula che funziona su tutti i dati del gruppo. Quando ad esempio si crea un totale per un anno, si crea un'aggregazione. Se quindi si crea un rapporto di quest'anno rispetto all'anno precedente e lo si presenta come percentuale, si tratta di un tipo diverso di aggregazione.
La decisione su come raggruppare i dati è guidata dalla domanda aziendale. Ad esempio, le aggregazioni possono rispondere alle domande seguenti:
Conteggi Quante transazioni ci sono state in un mese?
Medie Quali sono state le vendite medie in questo mese, per venditore?
Valori minimi e massimi Quali distretti di vendita sono stati i primi cinque in termini di unità vendute?
Per creare un calcolo che risponda a queste domande, è necessario disporre di dati dettagliati che contengano i numeri da contare o sommare e che tali dati numerici siano correlati in qualche modo ai gruppi che verranno usati per organizzare i risultati.
Se i dati non contengono già valori utilizzabili per il raggruppamento, ad esempio una categoria di prodotto o il nome della regione geografica in cui si trova il negozio, è consigliabile introdurre gruppi nei dati aggiungendo categorie. Quando si creano gruppi in Excel, è necessario digitare manualmente o selezionare i gruppi da usare tra le colonne del foglio di lavoro. In un sistema relazionale, tuttavia, le gerarchie, ad esempio le categorie per i prodotti, sono spesso archiviate in una tabella diversa da quella della tabella dei fatti o dei valori. Di solito la tabella delle categorie è collegata ai dati dei fatti da una sorta di chiave. Si supponga, ad esempio, di scoprire che i dati contengono ID prodotto, ma non i nomi dei prodotti o delle loro categorie. Per aggiungere la categoria a un foglio di lavoro flat di Excel, è necessario copiarla nella colonna contenente i nomi delle categorie. Con Power Pivot è possibile importare la tabella delle categorie di prodotti nel modello di dati, creare una relazione tra la tabella con i dati del numero e l'elenco delle categorie di prodotti e quindi usare le categorie per raggruppare i dati. Per altre informazioni, vedere Creare una relazione tra tabelle.
Scelta di una funzione per l'aggregazione
Dopo aver identificato e aggiunto i raggruppamenti da utilizzare, è necessario decidere quali funzioni matematiche utilizzare per l'aggregazione. Spesso la parola aggregazione viene usata come sinonimo per le operazioni matematiche o statistiche usate nelle aggregazioni, come somme, medie, minimi o conteggi. Tuttavia, Power Pivot consente di creare formule personalizzate per l'aggregazione, oltre alle aggregazioni standard disponibili in Power Pivot ed Excel.
Ad esempio, dato lo stesso set di valori e raggruppamenti utilizzati negli esempi precedenti, è possibile creare aggregazioni personalizzate che rispondano alle domande seguenti:
Conteggi filtrati Quante transazioni ci sono state in un mese, esclusa la finestra di manutenzione di fine mese?
Rapporti utilizzando le medie nel tempo Qual è stata la crescita o il calo percentuale delle vendite rispetto allo stesso periodo dell'anno scorso?
Valori minimi e massimi raggruppati Quali distretti di vendita si sono classificati al primo posto per ogni categoria di prodotto o per ogni promozione di vendita?
Aggiunta di aggregazioni a formule e tabelle pivot
Quando si avrà un'idea generale di come raggruppare i dati in modo che siano significativi e dei valori con cui si desidera lavorare, sarà possibile decidere se creare una tabella pivot o calcoli all'interno di una tabella. Power Pivot estende e migliora la capacità nativa di Excel di creare aggregazioni come somme, conteggi o medie. È possibile creare aggregazioni personalizzate in Power Pivot nella finestra Power Pivot o nell'area della tabella pivot di Excel.
- In una colonna calcolata è possibile creare aggregazioni che tengono conto del contesto della riga corrente per recuperare le righe correlate da un'altra tabella e quindi sommare, contare o calcolare la media di tali valori nelle righe correlate.
- In una misura è possibile creare aggregazioni dinamiche che usano sia i filtri definiti all'interno della formula sia i filtri imposti dalla progettazione della tabella pivot e dalla selezione di filtri dei dati, intestazioni di colonna e intestazioni di riga. Le misure che usano aggregazioni standard possono essere create in Power Pivot usando Somma automatica o creando una formula. È anche possibile creare misure implicite usando aggregazioni standard in una tabella pivot di Excel.
Aggiunta di raggruppamenti a una tabella pivot
Quando si progetta una tabella pivot, trascinare i campi che rappresentano raggruppamenti, categorie o gerarchie nella sezione delle colonne e delle righe della tabella pivot per raggruppare i dati. Quindi trascinare i campi che contengono valori numerici nell'area dei valori in modo che possano essere conteggiati, calcolati in media o sommati.
Se si aggiungono categorie a una tabella pivot ma i dati della categoria non sono correlati ai dati dei fatti, potrebbe verificarsi un errore o risultati particolari. In genere, Power Pivot tenta di risolvere il problema rilevando e suggerendo automaticamente le relazioni. Per altre informazioni, vedere Usare le relazioni nelle tabelle pivot.
È anche possibile trascinare i campi nei filtri dei dati per selezionare determinati gruppi di dati da visualizzare. I filtri dei dati consentono di raggruppare, ordinare e filtrare in modo interattivo i risultati in una tabella pivot.
Utilizzo dei raggruppamenti in una formula
È anche possibile usare raggruppamenti e categorie per aggregare i dati archiviati nelle tabelle creando relazioni tra le tabelle e quindi creando formule che sfruttano tali relazioni per cercare valori correlati.
In altre parole, se si vuole creare una formula che raggruppa i valori in base a una categoria, occorre prima usare una relazione per connettere la tabella contenente i dati dettaglio e le tabelle contenenti le categorie e quindi creare la formula.
Per altre informazioni su come creare formule che usano le ricerche, vedere Ricerche nelle formule di Power Pivot.
Utilizzo dei filtri nelle aggregazioni
Una nuova funzionalità di Power Pivot è la possibilità di applicare filtri a colonne e tabelle di dati, non solo nell'interfaccia utente e all'interno di una tabella pivot o di un grafico, ma anche nelle stesse formule usate per calcolare le aggregazioni. I filtri possono essere utilizzati nelle formule sia nelle colonne calcolate che nelle s.
Ad esempio, nelle nuove funzioni di aggregazione DAX, invece di specificare i valori da sommare o contare, è possibile specificare un'intera tabella come argomento. Se a tale tabella non sono stati applicati filtri, la funzione di aggregazione funzionerà su tutti i valori nella colonna specificata della tabella. Tuttavia, in DAX è possibile creare un filtro dinamico o statico per la tabella, in modo che l'aggregazione operi su un sottoinsieme diverso di dati a seconda della condizione di filtro e del contesto corrente.
Combinando condizioni e filtri nelle formule, è possibile creare aggregazioni che cambiano a seconda dei valori forniti nelle formule o a seconda della selezione di righe, intestazioni e intestazioni di colonna in una tabella pivot.
Per altre informazioni, vedere Filtrare i dati nelle formule.
Confronto tra le funzioni di aggregazione di Excel e le funzioni di aggregazione DAX
Nella tabella seguente sono elencate alcune delle funzioni di aggregazione standard fornite da Excel e sono disponibili collegamenti alla loro implementazione in Power Pivot. La versione DAX di queste funzioni si comporta in modo molto simile alla versione di Excel, con alcune piccole differenze nella sintassi e nella gestione di alcuni tipi di dati.
Funzioni di aggregazione Standard
| Funzione | Usare |
|---|---|
| MEDIA | restituisce la media aritmetica di tutti i numeri in una colonna. |
| AVERAGEA | restituisce la media aritmetica di tutti i valori di una colonna. Gestisce testo e valori non numerici. |
| CONTA.NUMERI | Conta il numero di valori numerici in una colonna. |
| CONTA.VALORI | Conta il numero di valori in una colonna che non sono vuoti. |
| MAX | restituisce il valore numerico più grande di una colonna. |
| MAXX | Restituisce il valore più grande di un set di espressioni valutato in una tabella. |
| MIN | restituisce il valore numerico più piccolo in una colonna. |
| MINX | Restituisce il valore più piccolo da un set di espressioni valutato su una tabella. |
| SOMMA | Somma tutti i numeri in una colonna. |
Funzioni di aggregazione DAX
DAX include funzioni di aggregazione che consentono di specificare una tabella in cui deve essere eseguita l'aggregazione. Pertanto, anziché limitarsi ad aggiungere o calcolare la media dei valori in una colonna, queste funzioni consentono di creare un'espressione che definisce dinamicamente i dati da aggregare.
Nella tabella seguente sono elencate le funzioni di aggregazione disponibili in DAX.
| Funzione | Usare |
|---|---|
| MEDIA X | Calcola la media di un set di espressioni valutate su una tabella. |
| COUNTAX | Conta un set di espressioni valutate su una tabella. |
| COUNTBLANK | Conta il numero di valori vuoti in una colonna. |
| CONTA | Conta il numero totale di righe in una tabella. |
| COUNTROWS | Conta il numero di righe restituite da una funzione di tabella annidata, come la funzione di filtro. |
| SUMX | restituisce la somma di un set di espressioni valutate in una tabella. |
Differenze tra DAX ed Excel Funzioni di aggregazione
Anche se queste funzioni hanno gli stessi nomi delle controparti di Excel, usano il motore di analisi in memoria di Power Pivot e sono state riscritte per funzionare con tabelle e colonne. Non è possibile usare una formula DAX in una cartella di lavoro di Excel e viceversa. Possono essere usati solo nella finestra di Power Pivot e nelle tabelle pivot basate su dati di Power Pivot. Inoltre, sebbene le funzioni abbiano nomi identici, il comportamento potrebbe essere leggermente diverso. Per altre informazioni, vedere gli argomenti di riferimento per le singole funzioni.
Anche il modo in cui le colonne vengono valutate in un'aggregazione è diverso dal modo in cui Excel gestisce le aggregazioni. Un esempio può aiutare a illustrare.
Si supponga di voler ottenere una somma dei valori nella colonna Importo della tabella Vendite, quindi creare la formula seguente:
=SUM('Sales'[Amount])
Nel caso più semplice, la funzione ottiene i valori da una singola colonna non filtrata e il risultato è uguale a quello di Excel, che somma sempre i valori nella colonna Importo. In Power Pivot, tuttavia, la formula viene interpretata come "Ottenere il valore in Importo per ogni riga della tabella Vendite e quindi sommare i singoli valori. Power Pivot valuta ogni riga su cui viene eseguita l'aggregazione, calcola un singolo valore scalare per ogni riga, quindi esegue un'aggregazione su tali valori. Di conseguenza, il risultato di una formula può essere diverso se sono stati applicati filtri a una tabella o se i valori vengono calcolati in base ad altre aggregazioni che potrebbero essere filtrate. Per altre informazioni, vedere Contesto nelle formule DAX.
Funzioni di intelligence basata sulle gerarchie temporali di DAX
Oltre alle funzioni di aggregazione tra tabelle descritte nella sezione precedente, DAX dispone di funzioni di aggregazione che funzionano con date e ore specificate dall'utente per fornire informazioni di intelligence per le gerarchie temporali predefinite. Queste funzioni usano intervalli di date per ottenere valori correlati e aggregare i valori. È anche possibile confrontare i valori tra intervalli di date.
Nella tabella seguente sono elencate le funzioni di intelligence per le gerarchie temporali che possono essere usate per l'aggregazione.
| Funzione | Usare |
|---|---|
|
CHIUSURASALDOMESE CHIUSURATRIMESTRETRIMESTRE CHIUSURAANNODI BILANCIO |
Calcola un valore alla fine del periodo specificato. |
|
OPENINGBALANCEMONTH APERTURABILANCIERETRIMESTRE ANNO DI APERTURA DEL BILANCIO |
Calcola un valore alla fine del calendario del periodo precedente al periodo specificato. |
|
TOTALMTD TOTALYTD TOTALQTD |
Calcola un valore nell'intervallo che inizia il primo giorno del periodo e termina all'ultima data nella colonna della data specificata. |
Le altre funzioni nella sezione relativa alla funzionalità di limitazione delle gerarchie temporali (funzioni di limitazione dell'affidabilità gerarchica) sono funzioni che possono essere usate per recuperare date o intervalli personalizzati di date da usare nell'aggregazione. È ad esempio possibile usare la funzione DATESINPERIOD per restituire un intervallo di date e usare tale set di date come argomento per un'altra funzione per calcolare un'aggregazione personalizzata solo per tali date.