Resultatrapporter i Power BI del 1

Power BI har over tid utviklet seg til å bli et av standardverktøyene til enhver økonom. I Power BI kan en gjøre mye rett “out of the box”, men det å sette opp en enkel resultatrapport med formatering som forventes av enhver økonom krever noe innsikt i hvordan Power BI fungerer.

Som med alt annet i Power BI kan dette gjøres veldig enkelt og veldig komplisert. Denne artikkelen vil ta for seg den aller enkleste metoden. Vi vi ta i bruk DAX funksjoner som SUM, CALCULATE, ALL, FILTER, MAX, DIVIDE , SELECTEDVALUE, FORMAT og SWITCH. Vi vil ta for oss mer avansert bruk i senere artikler.

Power BI filen brukt i artikkelen finner du på denne linken

1. Relasjoner og datamodellering

For å kunne sette opp en rapport i Power BI lik bildet fra Excel over er det behov for en ekstra tabell i Query settet som du kanskje ikke har vært borti før.

Vi kaller den LU_PLtableSimple. Denne tabellen skal hjelpe oss med å gruppere kontoer, sortere de i riktig rekkefølge, gjøre kalkulasjoner samt gi oss støtte i hvordan vi vil formattere de enkelte radene.

1. Sort forteller noe om rekkefølgen på radene, samt hvilke rader som skal være inkludert i Running Total radene
2. Description er beskrivelsen på raden
3. ReportKey er koblingen til kontoplanen og forteller hvilke kontoer som skal summeres opp på hver rad
4. Type skal hjelpe oss med både kalkulering og formatering

Det er også lagt til en kalkulert kolonne i LU_PLtableSimple. Vi kaller den RowDescription.

Grunnen til at vi lager denne er at Power BI trenger unike verdier per rad for å kunne sortere en tabell utover alfabetisk og numerisk sortering. Forsøker vi å sortere Description på Sort kolonnen vil vi få feilmeldingen i bildet under. Dette fordi Description på radene som har Type: SPACE1, SPACE2….. har samme verdi: null/blank

Den kalkulerte kolonnen RowDescription, legger inn mellomrom ihht tallverdien som står bak SPACE. Dvs SPACE1 får ett mellomrom, SPACE2 får to mellomrom etc.

En kan nå sortere RowDescription på Sort kolonnen slik at radene kommer i den rekkefølgen vi har definert.

2. DAX

Det første vi gjør når det kommer til å lage measures er å summere alle radene i Faktatabellen FA_ActualsSimple, det gjør vi med en enkel SUM formel. Dette measuret skal være grunnlaget for alle andre measures vi skal lage i modellen, et såkalt base measure. Vi kaller den amountSimple

I de aller fleste regnskapssystem ligger inntekter med negativt fortegn og kostnader med positivt fortegn. Vi snur fortegnet ved å sette et minustegn foran SUM formelen slik at inntekter blir positive og kostnader blir negative.

Legger man amountSimple inn i en vanlig tabell med Account og AccountName fra LU_COASimple, får man følgende resultat.

Legger man amountSimple inn i en vanlig tabell med RowDescription fra LU_PLtableSimple får man de konsoliderte radene basert relasjonen per RowKey. Men kun for de radene det eksisterer verdier.

Som standard i Power BI tabeller er “Show Items with no values” deaktivert, dersom man aktiverer denne vil en vise alle rader for RowDescription selv om raden ikke har en verdi

For å fylle ut Total revenue, EBITDA, EBIT og EBT må vi summere alle input-nivå radene over den aktuelle raden.

• Total revenue skal være sum av Service Revenue og Product Revenue.
• EBITDA skal være en sum av Service Revenue, Product Revenue, Material Cost, Labour Cost og Other Cost
• EBIT og EBT følger samme mønster

For å gjøre dette må vi kalkulere en Running Total

RunningTotalSimple baserer seg på amountSimple men vi tar i bruk CALCULATE som setter oss i stand til å manipulere filtreringen.

Hver funksjon for seg

La oss ta hver funksjon for seg for å prøve å forstå hva som skjer:

• ALL opphever alle filtere for en tabell eller en kolonne i en tabell
• FILTER returnerer en tabell med de filterene som er satt
• Den siste setningen i koden sier at measuret skal returnere alle rader som er lik ‘=’ eller mindre ‘<’ enn den aktuelle raden. Dvs at dersom vi står på rad med Sort = 30000 skal measuret returnere rader med Sort = 30000, 20000 og 10000

Leser vi koden fra innsiden og ut ser vi at:

1. Vi opphever alle filtre på LU_PLtableSimple med ALL ( ‘LU_PLtableSimple’ )
2. Vi sette filter på resultatet av steg 1 med FILTER, og sier at vi skal returnere alle rader som er lik ‘=’ eller mindre ‘<’ enn den aktuelle raden
3. Dette skal gjøres for measuret amountSimple

Legger vi RunningTotalSimple inn i forrige tabell sammen med amountSimple får vi følgende resultat

amountSimple viser verdier på input-nivå rader, mens RunningTotalSimple viser verdier på alle rader, uavhengig om det er på input-nivå, sum-nivå eller en blank rad.

amountSimple og RunningTotalSimple hver for seg gir dette oss lite, men om vi kan kombinere de to er vi nærmere målet. Mer om det litt senere, nå skal vi gjøre den siste kalkulasjonen, nemlig prosent-radene.

EBITDA %, EBIT % og EBT % har en ting til felles. Nevneren i brøken er Total Revenue for alle tre.

Vi ønsker derfor å lage et measure som returnerer Total Revenue på alle rader uavhengig av hvilken rad det er snakk om.

Dette gjør vi ved hjelp av funksjonene CALCULATE og ALL.

1. ALL ( ‘LU_PLtableSimple'[RowDescription], ‘LU_PLtableSimple'[ReportKey] ) opphever filter på RowDescription og RowKey
2. ‘LU_PLtableSimple'[Sort] <= 30000 setter på nytt filter, og gjør at RevenueSimple returnerer Total Revenue på alle rader.

Legger vi RevenueSimple inn i forrige tabell ser vi at vi har fått akkurat der resultatet vi var ute etter.

For å regne ut selve prosenten lager vi et ny measure der vi tar i bruk DIVIDE funksjonen.

Legger vi percentageSimple inn i tabellen vår ser vi at vi får et prosent-tall på hver rad. Igjen, hver for seg gir amountSimple, RunningTotalSimple og percentageSimple oss lite, men kombinert vil de gi oss akkurat det vi er på jakt etter.

Så til selve finalen der vi kombinerer de tre typene verdier inn i samme measure.

Dette gjøres ved bruk av SWITCH funksjonen. SWITCH er en type IF funksjon som mange kjenner fra Excel, men syntaxen er langt lettere å jobbe med når det er snakk om mange IF-statments, såkalt nested IF statement.

For at SWITCH skal vite den skal returnere hvilken verdi bruker vi SELECTEDVALUE.

Koden under kan leses på følgende måte

• Rad 4 sier at hvis LU_PLtableSimple[Type] er “0” så skal amountSimple vises
• Rad 5 sier at hvis LU_PLtableSimple[Type] er “2” så skal RunningTotalSimple vises
• Rad 6 sier at hvis LU_PLtableSimple[Type] er “3” så skal percentageSimple vises

for rad 6 må vi legge på FORMAT ettersom formatet på percentageSimple ikke følger med inn i SWITCH formelen. Dette kan løses på andre måter, men dette vil vi gå nærmere inn på i en av de neste artiklene om temaet.

Legger vi til finalSimple i tabellen vår ser vi at vi er i mål med å vise de korrekte verdiene per rad.

3. Formatering

For å bruke conditional formatting i Power BI, trenger vi en numerisk verdi for de radene vi ønsker å formatere.

Vi kan bruke Type kolonnen i LU_PLtableSimple tabellen, men denne er formatert som “text”.

Vi bruker derfor samme teknik som i finalSimple for å velge hva som skal returneres i measuret.