Cambio di calcoli in una tabella pivot con filtri dei dati

Gli slicer nelle tabelle pivot possono essere utilizzati non solo nel modo classico, per filtrare i dati di origine, ma anche per passare da un tipo di calcolo all'altro nell'area del valore:

Implementarlo è molto semplice: tutto ciò di cui hai bisogno sono un paio di formule e una tabella ausiliaria. Bene, faremo tutto questo non nel solito riepilogo, ma nel riepilogo costruito secondo il modello di dati Power Pivot.

Passaggio 1. Collegamento del componente aggiuntivo Power Pivot

Se le schede del componente aggiuntivo Power Pivot non sono visibili in Excel, dovrai prima abilitarlo. Ci sono due opzioni per questo:

• Linguetta sviluppatore - pulsante Componenti aggiuntivi COM (Sviluppatore: componenti aggiuntivi COM)
• File – Opzioni – Componenti aggiuntivi – Componenti aggiuntivi COM – Vai (File — Opzioni — Componenti aggiuntivi — Componenti aggiuntivi COM — Vai a)

Se questo non aiuta, prova a riavviare Microsoft Excel.

Passaggio 2: caricare i dati nel modello di dati di Power Pivot

Avremo due tabelle come dati iniziali:

Il primo è una tabella con le vendite, in base alla quale costruiremo in seguito un riepilogo. La seconda è una tabella ausiliaria, dove vengono inseriti i nomi dei pulsanti della futura slice.

Entrambe queste tabelle devono essere convertite in "intelligenti" (dinamiche) con una scorciatoia da tastiera Ctrl+T o squadra Home – Formatta come una tabella (Home — Formatta come tabella) ed è desiderabile dare loro nomi sani di mente nella scheda Costruttore (Design). Lascia che sia, per esempio, Vendite и Servizi.

Successivamente, ogni tabella a sua volta deve essere caricata nel modello di dati, per questo utilizziamo nella scheda PowerPivot pulsante Aggiungi al modello di dati (Aggiungi al modello di dati).

Passaggio 3. Creare una misura per determinare il pulsante premuto sulla fetta

Vengono chiamati i campi calcolati in una tabella pivot per modello di dati analisi. Creiamo una misura che visualizzerà il nome del pulsante premuto sulla fetta futura. Per fare ciò, in una qualsiasi delle nostre tabelle, seleziona una cella vuota nel pannello di calcolo inferiore e inserisci la seguente costruzione nella barra della formula:

Qui, il nome della misura viene prima (Pulsante premuto), e poi, dopo i due punti e il segno di uguale, una formula per calcolarlo utilizzando la funzione VALORI DAX integrato in Power Pivot.

Se lo ripeti non in Power Pivot, ma in Power BI, i due punti non sono necessari e invece VALORI puoi usare la sua controparte più moderna: la funzione VALORE SELEZIONATO.

Non prestiamo attenzione agli errori nella parte inferiore della finestra che compaiono dopo aver inserito la formula: sorgono, perché non abbiamo ancora un riepilogo e una fetta in cui viene fatto clic su qualcosa.

Passaggio 4. Creare una misura per il calcolo sul pulsante premuto

Il passaggio successivo consiste nel creare una misura per diverse opzioni di calcolo a seconda del valore della misura precedente Pulsante premuto. Qui la formula è un po' più complicata:

Analizziamolo pezzo per pezzo:

1. Funzione INTERRUTTORE – un analogo dell'IF nidificato – verifica il rispetto delle condizioni specificate e restituisce valori diversi a seconda del rispetto di alcune di esse.
2. Funzione VERO() – fornisce un "vero" logico in modo che le condizioni verificate successivamente dalla funzione SWITCH funzionino solo se sono soddisfatte, ovvero verità.
3. Quindi controlliamo il valore della misura del pulsante premuto e calcoliamo il risultato finale per tre diverse opzioni: come la somma del costo, il controllo medio e il numero di utenti unici. Per contare i valori univoci, utilizzare la funzione CONTO DISTINTO, e per arrotondare – ROTONDO.
4. Se nessuna delle tre condizioni precedenti è soddisfatta, viene visualizzato l'ultimo argomento della funzione SWITCH: lo impostiamo come dummy usando la funzione VUOTO().

Passaggio 5. Creazione di un riepilogo e aggiunta di una fetta

Resta da tornare da Power Pivot a Excel e creare lì una tabella pivot per tutti i nostri dati e misure. Per fare ciò, nella finestra di PowerPivot attiva Il principale comando di selezione della scheda tabella riassuntiva (Home — Tabella pivot).

Quindi:

1. Lanciamo il campo Prodotto dalla tabella Vendite alla zona Righe (righe).
2. Lanciare un campo lì Risultato dalla tabella Servizi.
3. Fare clic con il tasto destro sul campo Risultatoe scegli una squadra Aggiungi come fetta (Aggiungi come affettatrice).
4. Lanciando la seconda misura Сonclusione dalla tabella Servizi alla zona Valori (I valori).

Ecco, infatti, tutti i trucchi. Ora puoi fare clic sui pulsanti dell'affettatrice e i totali nella tabella pivot passeranno alla funzione di cui hai bisogno.

Bellezza 🙂

• Vantaggi di Pivot per modello di dati
• Analisi dei fatti in una tabella pivot su Power Pivot
• Crea un database in Excel usando il componente aggiuntivo Power Pivot