Når de først lærer hvordan du bruker Power Pivot, oppdager de fleste brukerne at den reelle kraften er i å aggregere eller beregne et resultat på en eller annen måte. Hvis dataene har en kolonne med numeriske verdier, kan du enkelt aggregere den ved å velge den i en pivottabell eller Power View Feltliste. Fordi den er numerisk, summeres den automatisk, beregnes i gjennomsnitt, telles eller hvilken type aggregasjon du velger. Dette kalles et implisitt mål. Implisitte mål er flotte for rask og enkel aggregering, men de har grenser, 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 og så videre. Vi har en annen relatert tabell kalt Produktkategori som inneholder en kolonne ProductCategoryName. Vi ønsker at hvert produkt i Produkt-tabellen skal inkludere produktkategorinavnet fra Produktkategori-tabellen. I Produkt-tabellen kan vi opprette en beregnet kolonne kalt Produktkategori som dette:
Den nye produktkategoriformelen bruker RELATERT DAX-funksjonen til å hente verdier fra ProductCategoryName-kolonnen i den relaterte Produktkategori-tabellen, og skriver deretter inn disse verdiene for hvert produkt (hver rad) i Produkt-tabellen.
Dette er et flott 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 pivottabellen eller i en Power View rapport.
La oss opprette et annet eksempel der vi vil beregne en fortjenestemargin for produktkategoriene våre. Dette er et vanlig scenario, selv i mange veiledninger. Vi har en salgstabell i datamodellen vår som har transaksjonsdata, og det er en relasjon mellom salgstabellen og Produktkategori-tabellen. I Salg-tabellen har vi en kolonne som har salgsbeløp og en annen kolonne som har kostnader.
Vi kan opprette en beregnet kolonne som beregner et fortjenestebeløp for hver rad ved å trekke verdier i cogs-kolonnen fra verdier 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 pivottabell). Resultatet er et implisitt mål kalt Fortjenestesum. Det er en aggregert mengde verdier fra fortjenestekolonnen for hver av de ulike produktkategoriene. Resultatet vårt ser slik ut:
I dette tilfellet gir Fortjeneste bare mening som et felt i VERDIER. Hvis vi skulle plassere Fortjeneste i KOLONNER-området, vil pivottabellen se slik ut:
Fortjeneste-feltet gir ikke nyttig informasjon når det er plassert i områder med KOLONNER, RADER eller FILTRE. 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. Vi la deretter til Fortjeneste i VERDIER-området i pivottabellen, og oppretter automatisk et implisitt mål, der et resultat beregnes for hver av produktkategoriene. Hvis du tror vi virkelig har beregnet fortjenesten for produktkategoriene våre to ganger, er du riktig. Vi beregnet først 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 trenger å opprette den beregnede fortjenestekolonnen, er du også riktig. Men hvordan beregner vi da fortjenesten uten å opprette en beregnet fortjenestekolonne?
Fortjeneste, ville egentlig vært bedre beregnet som et eksplisitt mål.
Foreløpig forlater vi kolonnen Beregnet fortjeneste i salgstabellen og produktkategorien i KOLONNER og Fortjeneste i VERDIER i pivottabellen for å sammenligne resultatene våre.
I beregningsområdet i salgstabellen skal vi opprette et mål kalt Totalfortjeneste(for å unngå navngivningskonflikter). Til slutt vil det gi de samme resultatene som det vi gjorde før, men uten en beregnet fortjenestekolonne.
Først velger vi SalesAmount-kolonnen i Salg-tabellen, og deretter klikker du Autosummer for å opprette en eksplisitt Sum of SalesAmount-måling. 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. Vi gir nytt navn til disse Totale salesAmount- og Total COGS-ene for å gjøre dem enklere å identifisere.
Deretter oppretter vi et nytt mål med denne formelen:
Totalfortjeneste:=[ Total salesAmount] - [Totalt antall coGS]
Obs!: Vi kan også skrive formelen vår som totalfortjeneste:=SUMMER([SalesAmount]) - SUM([COGS]), men ved å opprette separate mål for totalsalg og totale cogs kan vi bruke dem i pivottabellen også, og vi kan bruke dem som argumenter i alle slags andre målformler.
Når du har endret formatet for totalfortjeneste i valuta, kan vi legge det til i pivottabellen.
Du kan se 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 totalfortjenestemålet vårt er langt mer effektivt og gjør datamodellen renere og slankere fordi vi beregner på det tidspunktet og bare for feltene vi velger for pivottabellen vår. Vi trenger egentlig ikke den beregnede fortjenestekolonnen 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 å behandle ressurser for å beregne alle verdiene i Fortjeneste-kolonnen på nytt. Vi trenger egentlig ikke å ta opp ressurser som dette fordi vi virkelig ønsker å beregne fortjenesten vår 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 oppretter resultater som ved første øyekast ser riktige ut, men ......
I dette eksemplet vil vi beregne salgsbeløp som en prosentdel av totalt salg. Vi oppretter en beregnet kolonne kalt % av salg i salgstabellen vår, slik som dette:
Formeltilstandene våre: For hver rad i Salg-tabellen deler du beløpet i SalesAmount-kolonnen med summen 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 totalsum på % av salg for hver av produktkategoriene våre.
ok. Dette ser bra ut så langt. Men la oss legge til en slicer. Vi legger til kalenderåret og velger deretter et år. I dette tilfellet velger vi 2007. Dette er hva vi får.
Ved første øyekast kan dette fortsatt se riktig ut. Men prosentene våre bør egentlig utgjøre 100 %, fordi vi vil vite prosentandelen av totalt salg for hver av produktkategoriene for 2007. Så hva gikk galt?
Kolonnen % av salg beregnet en prosent for hver rad som er verdien i SalesAmount-kolonnen delt på summen av alle verdiene i SalesAmount-kolonnen. Verdier 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 den 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 kontekst og filterkontekst på radnivå, som er det vi beskriver her.
Vi kan slette kolonnen % av salg beregnet fordi den ikke vil hjelpe oss. I stedet skal vi opprette et mål som beregner prosenten av totalt salg på riktig måte, uavhengig av filtre eller slicere som er brukt.
Husker du TotalSalesAmount-målet vi opprettet tidligere, det som ganske enkelt summerer SalesAmount-kolonnen? Vi brukte det som et argument i målingen totalfortjeneste, og vi skal bruke det på nytt som et argument i det nye beregnede feltet.
Tips!: Oppretting av eksplisitte mål som Total SalesAmount og Total COGS er ikke bare nyttige i en pivottabell eller rapport, men de er også nyttige som argumenter i andre mål når du trenger resultatet som et argument. Dette gjør formlene mer effektive og enklere å lese. Dette er god praksis for datamodellering.
Vi oppretter et nytt mål med følgende formel:
% av totalt salg:=([Totalt salgBeløp]) / CALCULATE([Total SalesAmount], ALLSELECTED())
Denne formelen sier: Del resultatet fra Total SalesAmount med summen av SalesAmount uten andre kolonne- eller radfiltre enn de som er definert i pivottabellen.
Tips!: Pass på å lese om FUNKSJONENE CALCULATE og ALLSELECTED 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-året. Hvis vi velger et annet år eller mer enn ett år i CalendarYear slicer, får vi nye prosenter for produktkategoriene våre, men totalsummen er fortsatt 100 %. Vi kan også legge til andre slicere og filtre. Målingen % av totalt salg vil alltid produsere en prosentdel av totalt salg uavhengig av eventuelle slicere eller filtre som er brukt. 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 kraften i tiltakene.
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 beregnings behov:
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 TILE BY 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 nummeret på måneden. Du kan opprette en beregnet kolonne som bare beregner månedsnummeret fra datoene i Dato-kolonnen. 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 vil være avhengig av de andre feltene du velger i en pivottabell.
-
Hvis du trenger å gjøre mer komplekse beregninger, for eksempel beregne et antall basert på et filter av en eller annen sortering, eller beregne et år over år eller varians, bruker du et beregnet felt.
-
Hvis du vil beholde størrelsen på arbeidsboken til et minimum og maksimere ytelsen, oppretter du så mange av beregningene som mulig. I mange tilfeller kan alle beregningene være mål, noe som reduserer arbeidsbokstørrelsen betydelig og øker oppdateringstiden raskere.
Husk at det ikke er noe galt med å opprette beregnede kolonner som vi gjorde med Fortjeneste-kolonnen, og deretter aggregere den 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 forstår disse to svært kraftige funksjonene i Power Pivot, bør du opprette den mest effektive og nøyaktige datamodellen du kan. Forhåpentligvis hjelper det du har lært her. Det finnes noen andre virkelig flotte ressurser der ute som kan hjelpe deg også. Her er noen få: Kontekst i DAX-formler,aggregasjoner i Power Pivotog DAX-ressurssenter. Og selv om det er litt mer avansert og rettet mot regnskaps- og økonomiteknikere, er eksempelet Fortjeneste- og tapsdatamodellering og -analyse med Microsoft Power Pivot i Excel lastet med flotte eksempler på datamodellering og formler.
