Når de først lærer å bruke Power Pivot, oppdager de fleste brukere at den virkelige kraften ligger i å aggregere eller beregne et resultat på en eller annen måte. Hvis dataene har en kolonne med numeriske verdier, kan du enkelt aggregere dem ved å velge dem i en pivottabell eller Power View-feltliste. Siden det er numerisk, blir det av natur automatisk summert, beregnet gjennomsnitt, tellet, eller hvilken type aggregasjon du velger. Dette er kjent som et implisitt mål. Implisitte mål er flotte for rask og enkel aggregering, men de har begrensninger, og disse grensene kan nesten alltid overvinnes med eksplisitte mål og beregnede kolonner.
La oss først se på et eksempel der vi bruker en beregnet kolonne til å legge til en ny tekstverdi for hver rad i en tabell kalt Produkt. Hver rad i Produkt-tabellen inneholder all slags informasjon om hvert produkt vi selger. Vi har kolonner for produktnavn, farge, størrelse, forhandlerpris osv. Vi har en annen relatert tabell kalt Product Category, som inneholder kolonnen ProductCategoryName. Det vi ønsker er at hvert produkt i Produkt-tabellen skal inkludere produktkategorinavnet fra Produktkategori-tabellen. Slik kan du opprette en beregnet kolonne kalt Produktkategori i Produkt-tabellen:
Den nye Product Category-formelen bruker den RELATERTE DAX-funksjonen til å hente verdier fra ProductCategoryName-kolonnen i den relaterte Product Category-tabellen, og deretter angir vi disse verdiene for hvert produkt (hver rad) i produkttabellen.
Dette er et godt eksempel på hvordan vi kan bruke en beregnet kolonne til å legge til en fast verdi for hver rad som vi kan bruke senere i området RADER, KOLONNER eller FILTRE i en pivottabell eller i en Power View-rapport.
La oss lage et annet eksempel der vi ønsker å beregne en fortjenestemargin for våre produktkategorier. Dette er en vanlig situasjon, selv i mange opplæringer. Vi har en Salg-tabell i datamodellen vår som inneholder transaksjonsdata, og det er en relasjon mellom Salg-tabellen og Produktkategori-tabellen. I Salg-tabellen har vi en kolonne med salgsbeløp og en annen kolonne med kostnader.
Vi kan opprette en beregnet kolonne som beregner et fortjenestebeløp for hver rad ved å trekke verdier i COGS-kolonnen fra verdiene i SalesAmount-kolonnen, slik som dette:
Nå kan vi opprette en pivottabell og dra Produktkategori-feltet til KOLONNER, og det nye Fortjeneste-feltet til VERDIER-området (en kolonne i en tabell i PowerPivot er et felt i feltlisten for pivottabellen). Resultatet er et implisitt mål kalt Sum of Profit. Det er en aggregert mengde verdier fra kolonnen fortjeneste for hver av de forskjellige produktkategoriene. Vårt resultat ser slik ut:
I dette tilfellet gir fortjeneste bare mening som et felt i VERDIER. Hvis vi skulle plassere Fortjeneste i KOLONNER-området, ville pivottabellen vår se slik ut:
Fortjeneste-feltet vårt gir ingen nyttig informasjon når det er plassert i KOLONNER-, RAD- eller FILTRE-områder. Det gir bare mening som en aggregert verdi i VERDIER-området.
Det vi har gjort, er å opprette en kolonne kalt Fortjeneste, som beregner en fortjenestemargin for hver rad i Salg-tabellen. Deretter la vi til Fortjeneste i VERDIER-området av pivottabellen, og opprettet automatisk et implisitt mål, der et resultat beregnes for hver av produktkategoriene. Hvis du tenker at vi virkelig beregnet fortjenesten for produktkategoriene våre to ganger, har du rett. Først beregnet vi en fortjeneste for hver rad i Salg-tabellen, og deretter la vi til fortjeneste i VERDIER-området der den ble aggregert for hver av produktkategoriene. Hvis du også tenker at vi egentlig ikke trengte å opprette den beregnede kolonnen Fortjeneste, har du også rett. Men hvordan beregner vi da fortjenesten uten å opprette en beregnet fortjenestekolonne?
Profitt, ville virkelig være bedre beregnet som et eksplisitt mål.
Inntil videre lar vi kolonnen Fortjeneste beregnede være i Salgstabell og Produktkategori i KOLONNER og Fortjeneste i VERDIER i pivottabellen for å sammenligne resultatene.
I beregningsområdet i Salg-tabellen skal vi opprette et mål kalt Total fortjeneste (for å unngå navngivningskonflikter). Til slutt vil det gi de samme resultatene som vi gjorde før, men uten en beregnet kolonne for fortjeneste.
Først velger vi SalesAmount-kolonnen i SalesAmount-tabellen, og deretter klikker vi Autosummer for å opprette et eksplisitt Sum of SalesAmount-mål . Husk at et eksplisitt mål er et mål vi oppretter i beregningsområdet i en tabell i Power Pivot. Vi gjør det samme for COGS-kolonnen. Disse vil endre navn til Total salesAmount og Total COGS for å gjøre dem enklere å identifisere.
Deretter oppretter vi et annet mål med denne formelen:
Total fortjeneste:=[Total SalesAmount] - [Total COGS]
Obs!
Vi kan også skrive formelen vår som Total Profit:=SUM([SalesAmount]) – SUM([COGS]), men ved å opprette separate Total SalesAmount- og Total COGS-mål, kan vi bruke dem i pivottabellen også, og vi kan bruke dem som argumenter i alle slags andre målformler.
Etter at vi har endret formatet på det nye Totalfortjeneste-målet til valuta, kan vi legge det til i pivottabellen.
Du kan se at vårt nye mål for totalfortjeneste returnerer de samme resultatene som å opprette en beregnet fortjenestekolonne og deretter plassere den i VERDIER. Forskjellen er at Totalfortjeneste-målet er langt mer effektivt og gjør datamodellen renere og enklere fordi vi beregner akkurat da og bare for feltene vi velger for pivottabellen. Vi trenger egentlig ikke den beregnede kolonnen for fortjeneste likevel.
Hvorfor er denne siste delen viktig? Beregnede kolonner legger til data i datamodellen, og data tar opp minne. Hvis vi oppdaterer datamodellen, er det også nødvendig med behandlingsressurser for å beregne alle verdiene i Fortjeneste-kolonnen på nytt. Vi trenger egentlig ikke å bruke ressurser som dette, fordi vi ønsker å beregne fortjenesten når vi velger feltene vi vil ha fortjeneste for i pivottabellen, for eksempel produktkategorier, område eller etter datoer.
La oss se på et annet eksempel. En der en beregnet kolonne genererer resultater som ved første øyekast ser riktige ut, men....
I dette eksemplet ønsker vi å beregne salgsbeløp som en prosentdel av totalt salg. Vi oppretter en beregnet kolonne som heter % av salg i Salg-tabellen, slik som dette:
Formelen vår sier: For hver rad i Salg-tabellen dividerer du beløpet i SalesAmount-kolonnen med totalsummen av alle beløpene i SalesAmount-kolonnen.
Hvis vi oppretter en pivottabell og legger til Produktkategori i KOLONNER og velger den nye kolonnen % av salg for å sette den inn i VERDIER, får vi en sum på % av salget for hver av produktkategoriene våre.
Greit. Dette ser bra ut så langt. Men la oss legge til en slicer. Vi legger til Calendar Year og velger deretter et år. I dette tilfellet velger vi 2007. Dette er hva vi får.
Ved første øyekast kan dette fremdeles se riktig ut. Men prosentandelene våre burde egentlig utgjøre 100 %, fordi vi ønsker å vite prosentandelen av totalt salg for hver av produktkategoriene våre for 2007. Så hva gikk galt?
Kolonnen % av salg beregnet en prosent for hver rad som er verdien i kolonnen SalesAmount, dividert med totalsummen av alle verdiene i kolonnen SalesAmount. Verdiene i en beregnet kolonne er faste. De er et uforanderlig resultat for hver rad i tabellen. Da vi la til % av salg i pivottabellen, ble det aggregert som en sum av alle verdiene i SalesAmount-kolonnen. Summen av alle verdiene i kolonnen % av salg vil alltid være 100 %.
Tips
Pass på å lese kontekst i DAX-formler. Det gir en god forståelse av radnivåkontekst og filterkontekst, som er det vi beskriver her.
Vi kan slette kolonnen % av salg beregnet, fordi det ikke kommer til å hjelpe oss. I stedet skal vi opprette et mål som beregner prosenten vår av totalt salg på en korrekt måte, uavhengig av eventuelle filtre eller slicere som brukes.
Husker du TotalSalesAmount-målet vi opprettet tidligere, det som ganske enkelt summerer SalesAmount-kolonnen? Vi brukte det som et argument i vårt Totalfortjeneste-mål, og vi skal bruke det på nytt som et argument i vårt nye beregnede felt.
Tips
Oppretting av eksplisitte mål som Totalt salgsbeløp og Totalt vareforbruk er ikke bare nyttige i seg selv i en pivottabell eller rapport, men de er også nyttige som argumenter i andre mål når du trenger resultatet som argument. Dette gjør formlene mer effektive og enklere å lese. Dette er god datamodelleringspraksis.
Vi oppretter et nytt mål med følgende formel:
% av totalt salg:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Denne formelen sier: Del resultatet fra Totalt SalesAmount med totalsummen av SalesAmount, uten noen kolonne- eller radfiltre bortsett fra de som er definert i pivottabellen.
Tips
Pass på at du leser om CALCULATE- og ALLSELECTED-funksjonene i DAX-referansen.
Hvis vi nå legger til vår nye % av totalt salg i pivottabellen, får vi:
Det ser bedre ut. Nå beregnes % av totalt salg for hver produktkategori som en prosentdel av totalt salg for 2007. Hvis vi velger et annet år eller mer enn ett år i CalendarYear-sliceren, får vi nye prosentdeler for produktkategoriene våre, men totalsummen er fortsatt 100 %. Vi kan legge til andre slicere og filtre også. Vårt mål for % av totalt salg vil alltid produsere en prosentandel av totalt salg, uavhengig av eventuelle slicere eller filtre som brukes. Med mål beregnes resultatet alltid i henhold til konteksten som bestemmes av feltene i KOLONNER og RADER, og av eventuelle filtre eller slicere som brukes. Dette er tiltakenes kraft.
Her er noen retningslinjer som kan hjelpe deg når du skal avgjøre om en beregnet kolonne eller et mål er riktig for et bestemt beregningsbehov:
Bruke beregnede kolonner
- Hvis du vil at de nye dataene skal vises på RADER, KOLONNER eller i FILTRE i en pivottabell, eller på en AKSE, FORKLARING eller SIDE VED side ved side i en Power View-visualisering, må du bruke en beregnet kolonne. På samme måte som vanlige kolonner med data, kan beregnede kolonner brukes som et felt i et hvilket som helst område, og hvis de er numeriske, kan de også aggregeres i VERDIER.
- Hvis du vil at de nye dataene skal være en fast verdi for raden. Du har for eksempel en datotabell med en kolonne med datoer, og du vil ha en annen kolonne som bare inneholder tallet for måneden. Du kan opprette en beregnet kolonne som bare beregner månedsnummeret ut fra datoene i Dato-kolonnen. For eksempel =MÅNED('Dato'[Dato]).
- Hvis du vil legge til en tekstverdi for hver rad i en tabell, bruker du en beregnet kolonne. Felt med tekstverdier kan aldri aggregeres i VERDIER. =FORMAT('Date'[Date],"mmmm") gir oss for eksempel månedsnavnet for hver dato i Dato-kolonnen i Dato-tabellen.
Bruk mål
- Hvis resultatet av beregningen alltid er avhengig av de andre feltene du velger i en pivottabell.
- Hvis du må utføre mer komplekse beregninger, for eksempel beregne et antall basert på et filter, eller beregne et år-til-år eller en avvik, bruker du et beregnet felt.
- Hvis du vil holde størrelsen på arbeidsboken til et minimum og maksimere ytelsen, oppretter du så mange av beregningene som mål som mulig. I mange tilfeller kan alle beregningene være mål, noe som reduserer størrelsen på arbeidsboken betydelig og gjør at oppdateringstiden går raskere.
Husk at det ikke er noe galt med å opprette beregnede kolonner som vi gjorde med kolonnen Fortjeneste, og deretter aggregere dem i en pivottabell eller rapport. Det er faktisk en veldig god og enkel måte å lære om og lage dine egne beregninger på. Etter hvert som du får større forståelse av disse to ekstremt kraftfulle funksjonene i Power Pivot, må du opprette den mest effektive og nøyaktige datamodellen du kan. Forhåpentligvis er det du har lært her, til hjelp. Det finnes noen andre virkelig gode ressurser der ute som kan hjelpe deg også. Her er noen få av dem: Kontekst i DAX-formler, aggregeringer i Power Pivot og DAX-ressurssenter. Selv om det er litt mer avansert og rettet mot regnskaps- og økonomieksperter, er eksempelet Modellering og analyse av resultatdata med Microsoft Power Pivot i Excel lastet med flotte datamodellering og formeleksempler.