Bytte beregninger i en pivottabell med slicere

Slicere i pivottabeller kan brukes ikke bare på den klassiske måten – for å filtrere kildedataene, men også for å bytte mellom ulike typer beregninger i verdiområdet:

Å implementere dette er veldig enkelt - alt du trenger er et par formler og en hjelpetabell. Vel, vi vil gjøre alt dette ikke i det vanlige sammendraget, men i sammendraget bygget i henhold til Power Pivot-datamodellen.

Trinn 1. Koble til Power Pivot-tillegget

Hvis fanene til Power Pivot-tillegget ikke er synlige i Excel, må du først aktivere det. Det er to alternativer for dette:

• Tab utvikler - knapp COM-tillegg (Utvikler – COM-tillegg)
• Fil – Alternativer – Tillegg – COM-tillegg – Gå (Fil — Alternativer — Tillegg — COM-tillegg — Gå til)

Hvis dette ikke hjelper, prøv å starte Microsoft Excel på nytt.

Trinn 2: Last inn data i Power Pivot-datamodellen

Vi vil ha to tabeller som startdata:

Den første er en tabell med salg, som vi senere skal bygge et sammendrag i henhold til. Den andre er en hjelpetabell, der navnene på knappene til den fremtidige skiven legges inn.

Begge disse tabellene må konverteres til "smart" (dynamisk) med en hurtigtast Ctrl+T eller team Hjem – Formater som en tabell (Hjem — Formater som tabell) og det er ønskelig å gi dem fornuftige navn på fanen Constructor (Design). La det være f.eks. Salg и Tjenester.

Etter det må hver tabell i sin tur lastes inn i datamodellen - for dette bruker vi på fanen Powerpivot knapp Legg til datamodell (Legg til datamodell).

Trinn 3. Lag et mål for å bestemme knappen som er trykket på skiven

De beregnede feltene i en pivottabell etter datamodell kalles målinger. La oss lage et mål som viser navnet på knappen som trykkes på den fremtidige skiven. For å gjøre dette, i en av våre tabeller, velg en tom celle i det nedre beregningspanelet og skriv inn følgende konstruksjon i formellinjen:

Her kommer navnet på tiltaket først (Trykket på knappen), og deretter etter et kolon og et likhetstegn, en formel for å beregne det ved hjelp av funksjonen VERDIER DAX innebygd i Power Pivot.

Hvis du gjentar dette ikke i Power Pivot, men i Power BI, er det ikke nødvendig med kolon og i stedet VERDIER du kan bruke dets mer moderne motstykke – funksjonen VALGT VERDI.

Vi tar ikke hensyn til feil i den nedre delen av vinduet som vises etter å ha skrevet inn formelen - de oppstår fordi vi ennå ikke har et sammendrag og en skive der noe klikkes.

Trinn 4. Lag et mål for beregningen på den trykket knappen

Neste trinn er å lage et mål for ulike beregningsalternativer avhengig av verdien av forrige mål Trykket på knappen. Her er formelen litt mer komplisert:

La oss bryte det ned bit for bit:

1. Funksjon SWITCH – en analog av nestet IF – sjekker oppfyllelsen av de spesifiserte betingelsene og returnerer forskjellige verdier avhengig av oppfyllelsen av noen av dem.
2. Funksjon EKTE() – gir en logisk "true" slik at betingelsene som senere sjekkes av SWITCH-funksjonen fungerer bare hvis de er oppfylt, dvs. sannhet.
3. Deretter sjekker vi verdien av det knappetrykket mål og beregner sluttresultatet for tre forskjellige alternativer – som summen av kostnaden, gjennomsnittssjekken og antall unike brukere. For å telle unike verdier, bruk funksjonen DISTINCTCOUNT, og for avrunding – ROUND.
4. Hvis ingen av de tre betingelsene ovenfor er oppfylt, vises det siste argumentet til SWITCH-funksjonen – vi setter det som en dummy ved å bruke funksjonen BLANK().

Trinn 5. Bygg et sammendrag og legg til et stykke

Det gjenstår å gå tilbake fra Power Pivot til Excel og bygge en pivottabell der for alle våre data og mål. For å gjøre dette, i Power Pivot-vinduet på Den viktigste tab velg kommando oppsummeringstabell (Hjem – pivottabell).

Deretter:

1. Vi kaster feltet Produkt fra tabellen Salg til området Rader (rader).
2. Kaster et felt der Resultat fra tabellen Tjenester.
3. Høyreklikk på feltet Resultatog velg et lag Legg til som skive (Legg til som skjærer).
4. Kaster den andre takten Konklusjon fra tabellen Tjenester til området Verdier (Verdier).

Her er faktisk alle triksene. Nå kan du klikke på slicer-knappene – og summene i pivottabellen vil bytte til funksjonen du trenger.

Skjønnhet 🙂

• Fordeler med Pivot by Data Model
• Planfaktaanalyse i en pivottabell på Power Pivot
• Opprett en database i Excel ved å bruke Power Pivot-tillegget