I dette blogindlæg kigger vi på, hvordan vi kan tilpasse vores DAX measure til at udregne gennemsnittet af lavere hierarkiers gennemsnit - som f.eks. gennemsnittet af det månedlige gennemsnit i en årstotal. Problemstillingen er vist ud fra en matrixvisualisering, men vil også fungere i andre visualiseringer.
I Power BI bliver measures evalueret ud fra den filterkontekst, der er på et measure. Så vi har en tabel, der på et rækkeniveau viser, hvor meget vi har solgt af et produkt, til hvilket land, på en bestemt dato. I denne tabel har vi så en linje indikator, som er lig med 1 for alle linjer og en indikator, som angiver, om ordren blev leveret til tiden.
En interessant case i dette tilfælde ville være at finde ud, af hvor mange af vores linjer, der blev leveret til tiden. For at finde ud af dette, laver vi 3 measures i DAX:
De tre measures vil vi gerne vise i en matrix visual med et dato-hierarki, der viser år og måned med subtotaler pr. år.
Værdien for On Time Delivered Sales Order Lines % bliver udregnet i den filterkontekst, der er på rækken i matrixvisualiseringen, hvilket betyder, at divisionen udføres pr. række - også i årstotalen:
I totalen for 2018 får vi summen på tværs af alle måneder: Der er 3 ud af 6 salgsordre-linjer, som er blevet leveret til tiden.
Men hvad nu hvis vi med vores procent er mere interesseret i at se gennemsnittet af vores månedlige OTD % for det gældende år?
Dette kan være relevant for mange rapporter, hvor vi på årsbasis gerne vil evaluere den gennemsnitlige månedlige performance -i stedet for totalen - da de månedlige svingninger ikke fanges lige så godt.
Altså: (100 + 100 + 0)/3
Hvilket giver 66,67% i stedet for 50,00%.
For at vise dette, er vi nødt til at ændre vores DAX kode for On Time Delivered %.
Du har måske lært, at du skal bruge AVERAGEX, når du vil evaluere noget på rækkeniveau, inden du finder et gennemsnit. Så det forsøger vi os med i vores nye formel for OTD%:
Dette betyder, at vi for hver række i ’Fact Sales’ udfører udregningen for on time delivery %.
Vores total er dog fortsat forkert. Hvorfor det?
Udregningen for On Time Delivery % vil blive enten 1 eller 0 (ref. vores data), så gennemsnittet i totalen vil ikke være et gennemsnit af vores månedlige OTD%, men et gennemsnit af 100% og 0% OTD på rækkeniveau i ’Fact Sales’.
Vi bliver derfor nødt til at få aggregeret vores data op til et månedsniveau (lige som vi præsenterer det i vores matrix), før vi udfører OTD% udregningen.
Introducing SUMMARIZE
SUMMARIZE er en funktion i DAX, der kan hjælpe os med at få aggregeret vores data til f.eks. at lave en calculated table, som kan bruges enten direkte i en datamodel eller som reference i et DAX measure. Vi starter ud med at lave en calculated table, hvis udregning vi kan verificere, inden vi inkluderer det i vores OTD% New measure.
Da vi ønsker at evaluere månedsgennemsnittet, skal vi have specificeret, at det er på månedsbasis, vi ønsker at få aggregeret vores data. Så vi specificerer, at kolonnerne vi grupperer på er Year og Month fra Fact Sales. Derefter kan vi specificere nogle udregninger, der skal evalueres - for hver række i vores aggregerede tabel. Dette specificeres ved at først angive et navn for den nye kolonne -f.eks. ”No Lines Delivered”, efterfulgt af et komma - for at markere ny blok - og derefter udregningen, som allerede ligger i det measure, vi tidligere har lavet:
Vi får så en calculated table, kaldet Aggregate fact sales, der ligner den matrixvisualisering, som vi har i vores rapport:
Det er værdierne i kolonnen ”On Time Delivered %” fra Aggregate fact sales, vi gerne vil tage gennemsnittet af. Vi inkluderer dermed calculated table-udtrykket i vores DAX for OTD% New:
Derefter ser vi dette i vores matrice visualisering:
OTD % New har nu den korrekte værdi i rækkerne, men i totalen giver den ikke længere totalen evalueret på rækken, men gennemsnittet af værdierne observeret pr. måned.