Når du kan bruke beregnede kolonner og beregnede felt

Når de først lærer hvordan de bruker Power Pivot, oppdager de fleste brukere at den virkelige 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 dem ved å velge dem i en pivottabell eller power view-feltliste. Fordi det er numerisk, summeres det automatisk, beregnes gjennomsnitt, telles 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 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 osv.. Vi har en annen relatert tabell kalt Produktkategori som inneholder en kolonne ProductCategoryName. Det vi ønsker er at hvert produkt i Produkt-tabellen skal inkludere produktkategorinavnet fra tabellen Produktkategori. I produkttabellen kan vi opprette en beregnet kolonne kalt Produktkategori som dette:

Vår nye produktkategoriformel bruker RELATED DAX-funksjonen til å hente verdier fra kolonnen ProductCategoryName i den relaterte produktkategoritabellen, og angir deretter disse verdiene for hvert produkt (hver rad) i Produkt-tabellen.

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 RAD-, KOLONNE- eller FILTRE-området i pivottabellen eller i en Power View-rapport.

La oss opprette et annet eksempel der vi ønsker å beregne en fortjenestemargin for produktkategoriene våre. Dette er et vanlig scenario, selv i mange opplæringer. Vi har en Salgstabell i datamodellen som inneholder transaksjonsdata, og det er en relasjon mellom Salg-tabellen 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 VAREFORBRUK-kolonnen fra verdier i Kolonnen SalesAmount, slik:

Nå kan vi opprette en pivottabell og dra produktkategorifeltet til KOLONNER, og det nye Fortjeneste-feltet til VALUES-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 fortjenestekolonnen for hver av de ulike produktkategoriene. Resultatet vårt ser slik ut:

I dette tilfellet gir fortjeneste bare mening som et felt i VALUES. Hvis vi plasserer Fortjeneste i KOLONNER-området, vil pivottabellen se slik ut:

Fortjeneste-feltet gir ingen nyttig informasjon når det plasseres i kolonne-, RAD- eller FILTRE-områder. Det gir bare mening som en aggregert verdi i VALUES-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 VALUES-området i pivottabellen, og opprettet automatisk et implisitt mål, der et resultat beregnes for hver av produktkategoriene. Hvis du tenker at vi virkelig beregnet fortjeneste for produktkategoriene våre to ganger, er du riktig. Vi beregnet først en fortjeneste for hver rad i Salg-tabellen, og vi la deretter til Fortjeneste i VALUES-området der den ble aggregert for hver av produktkategoriene. Hvis du også tenker at vi egentlig ikke trenger å opprette den beregnede kolonnen Fortjeneste, er du også riktig. Men hvordan beregner vi fortjenesten uten å opprette en beregnet fortjenestekolonne?

Fortjeneste, ville virkelig være bedre beregnet som et eksplisitt mål.

Foreløpig skal vi la vår fortjenesteberegnede kolonne være i Salg-tabellen og Produktkategori i KOLONNER og Fortjeneste i VERDIER i pivottabellen, for å sammenligne resultatene våre.

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 det vi gjorde før, men uten en beregnet resultatkolonne.

Først velger vi Kolonnen SalesAmount i Salg-tabellen, og deretter klikker du Autosummer for å opprette en eksplisitt Sum av SalesAmount-mål. Husk at et eksplisitt mål er et vi oppretter i beregningsområdet i en tabell i Power Pivot. Vi gjør det samme for VAREFORBRUK-kolonnen. Vi gir nytt navn til dette totale salgsbeløpet og totalt antall vareforbruk for å gjøre dem enklere å identifisere.

Deretter oppretter vi et nytt mål med denne formelen:

Total fortjeneste:=[ Total SalesAmount] - [Total COGS]

Når vi har endret formatet for det nye totalfortjenesten til valuta, kan vi legge det til i pivottabellen.

Du kan se at vårt nye mål for total fortjeneste returnerer de samme resultatene som å opprette en beregnet fortjenestekolonne og deretter plassere den i VALUES. Forskjellen er at målet for total fortjeneste er langt mer effektivt og gjør datamodellen vår renere og slankere fordi vi beregner på det tidspunktet 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 dataene 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 å ta opp ressurser som dette fordi vi virkelig ø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 oppretter resultater som ved første øyekast ser riktig ut, men....

I dette eksemplet ønsker vi å beregne salgsbeløp som en prosentdel av det totale salget. Vi oppretter en beregnet kolonne med navnet % av salg i salg-tabellen, slik som dette:

Formelen vår sier: For hver rad i Salg-tabellen deler du beløpet i SalesAmount-kolonnen med TOTALSUMmen for alle beløp i Kolonnen SalesAmount.

Hvis vi oppretter en pivottabell og legger til produktkategori i KOLONNER og velger den nye kolonnen % av salg for å plassere den 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år, 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. Prosentene våre bør imidlertid totalt være 100 %, fordi vi ønsker å vite prosentdelen av det totale salget for hver av produktkategoriene våre for 2007. Så hva gikk galt?

Kolonnen % av salg beregnet en prosentverdi for hver rad som er verdien i Kolonnen SalesAmount dividert med totalsummen for alle verdiene i Kolonnen SalesAmount. Verdier i en beregnet kolonne er faste. De er et uforanderlig resultat for hver rad i tabellen. Da vi la til % av salget 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 %.

Vi kan slette den beregnede kolonnen % av salg fordi den ikke kommer til å hjelpe oss. I stedet skal vi opprette et mål som beregner prosenten av det totale salget på riktig måte, uavhengig av hvilke som helst 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 totalfortjenestemål, og vi skal bruke det på nytt som et argument i det nye beregnede feltet.

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 Total SalesAmount med summen av SalesAmount uten andre kolonne- eller radfiltre enn de som er definert i pivottabellen.

Hvis vi nå legger til den nye %en av totalt salg i pivottabellen, får vi:

Det ser bedre ut. Nå beregnes % av totalt salg for hver produktkategori som en prosentdel av det totale salget for 2007-året. 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 også legge til andre slicere og filtre. Vårt mål for % av totalt salg vil alltid produsere en prosentdel av det totale salget, uavhengig av hvilke som helst 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 kraften i mål.

Her er noen retningslinjer som kan hjelpe deg når du bestemmer deg for om en beregnet kolonne eller et mål er riktig for et bestemt beregningsbehov:

Bruk 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 FLIS ETTER i en Power View-visualisering, må du bruke en beregnet kolonne. Akkurat 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 i 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 VALUES. =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 å utføre mer komplekse beregninger, for eksempel beregne en telling basert på et filter av en eller annen sortering, eller beregne et år-over-år, eller varians, kan du bruke et beregnet felt.

• Hvis du vil holde størrelsen på arbeidsboken minimum og maksimere ytelsen, kan du opprette så mange av beregningene som mulig. I mange tilfeller kan alle beregningene være mål, noe som reduserer størrelsen på arbeidsboken betydelig og øker hastigheten på oppdateringstiden.

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 opprette dine egne beregninger på. Etter hvert som forståelsen av disse to svært kraftige funksjonene i Power Pivot vokser, 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 gode ressurser der ute som kan hjelpe deg også. Her er bare noen få: Kontekst i DAX-formler, aggregasjoner i Power Pivot og DAX Resource Center. Og selv om det er litt mer avansert og rettet mot regnskaps- og finansteknikere, lastes datamodellering og analyse for fortjeneste og tap med Microsoft Power Pivot i Excel inn med flotte datamodellerings- og formeleksempler.