Explosieve kracht in een cel: aggregaat berekeningen

Een serie blogs over Excel en Power BI.

BLOG – We gaan verder met analyseren, de derde laag van het vijflagen moden (delen, visualiseren, analyseren, voorbereiden verbinden). In mijn vorige blog heb je gekeken naar rij in kolom berekeningen. Vandaag neem ik je mee naar aggregaat berekeningen. Data is het nieuwe goud, maar aggregaat berekeningen laat dat goud glanzen. 

Door Henk Vlootman, adviseur, trainer, spreker en auteur, en topspecialist op het gebied van Microsoft Power BI en Power Query.

Hoewel Power Pivot het begrip cel niet kent, staan aggregaat berekeningen in iets dat op een cel lijkt. Dat komt omdat het resultaat van dit soort berekeningen in principe een waarde is. De (explosieve) kracht van een aggregaat berekening ligt in de mogelijkheid om de waarde uit miljoenen cellen of rijen in een positie weer te berekeningen. Kijk maar naar de Excel formule is afbeelding 2. Je ziet daar dat alle cellen tussen cel A2 en cel A1000001 worden opgeteld: dat zijn 1 miljoen cellen. In Power Pivot of Power BI kunnen die aantallen getallen nog meer oplopen. Daar tel je met de DAX SUM een kolom op. Bevat de tabel bijvoorbeeld 5 miljoen rijen, dan zie je echt het resultaat van de optelling van die 5 miljoen rijen.

Zoals je misschien al gelezen hebt in mijn vorige blog behoort een aggregaat berekening niet tot, en daarbij dus niet in, een tabel; het zijn losstaande berekeningen. Kijk je naar aggregaat berekeningen, dan blaast de hoeveelheid mogelijkheden van de verschillende DAX functies je wel eens van je sokken. Maar de bottom line van aggregaat berekeningen is dat het je in staat stelt om saaie, complexe en grote tabellen te ‘crunchen’ tot smakelijke, hapklare visualisaties. 

Een van de lastige begrippen is dat dezelfde functie of DAX berekening zowel in rij als in kolom berekeningen als aggregaat berekeningen toepasbaar zijn. Dat maakt het voor de beginner wel wat lastiger om te herkennen wat voor soort vlees je in de kuip hebt. In afbeelding 3 zie bijvoorbeeld een typische aggregaat berekening (de DAX SUM) in een rij in kolom berekening en in een aggregaat berekening.

Aggregaat berekeningen zijn ten opzichte van Excel veel prominenter aanwezig in Power BI en Power Pivot. Dit zijn namelijk de enige soort berekeningen, die je in de visualisaties gebruikt. In Excel is het mogelijk om een berekenende visualisaties te maken – dus niet door middel van een draaitabel – maar is het dan niet zo dat ik rij in kolom berekeningen in Power Pivot niet in een tabel kan tonen? Natuurlijk kan dat, maar dan alleen door middel van een aggregaat berekening en onder specifieke voorwaarden. Ben je nu de weg kwijtgeraakt?
Het antwoord ligt in hoe visualisaties in Power Pivot en Power BI werken. Alle – ja echt alle – visualisaties in die omgevingen werken namelijk altijd op basis van een draaitabel. In afbeelding 4 zie je een voorbeeld tabel. 

De kolom Totaal bestaat uit de rij in kolom vermenigvuldiging van de kolom Aantal en de kolom Prijs. Wil je nu de rij in de kolom berekening zichtbaar maken in je uitvoer, dan moet je eerst de kolom Totaal met de DAX SUM optellen. Gebruik je nu de aggregaat berekening in een draaitabel en filter je deze, dan zie je ook de individuele resultaten. Dat gaat alleen maar goed als je de rij waarop je filtert (in dit voorbeeld de producten) unieke resultaten bevat. Ik schat jouw kwaliteit zo hoog in, dat je zelf kunt bedenken waarom dat zo is. Afbeelding 5 laat je het resultaat zien.

Zoals gezegd zijn aggregaat berekeningen in Excel of Power Pivot vaak eenvoudig, maar soms ook zeer complex. Soms moet je heel diep en lang nadenken voordat je een complexe berekeningen maakt. Toch loont die inspanning. Omdat business vraagstukken nu eenmaal vaak complex zijn en organisch in tijd groeien, helpen dit soort berekeningen mee in het scherp(er) stellen van de business vragen en -antwoorden. En voordat je het beseft veranderen rapportages naar analyses. 

Rapportages kijken altijd naar het verleden, terwijl analyses beschikbare data gebruiken om de (nabije) toekomst te onderzoeken. Ik kan dan ook nooit genoeg nadruk leggen op het belang voor iedereen in de business, maar specifiek de financial, om te leren werken met data en daarmee ook met aggregatieberekeningen. Je ziet de kwaliteit en daarmee de positie van de financial stijgen als hij of zij beheersing heeft over data. En daarmee bedoel ik niet alleen financiële informatie, maar alle informatie in zijn organisatie.

Het belang van aggregaat berekeningen wordt in gereedschappen als Power Pivot of Power BI nog veel meer versterkt, doordat deze programma’s werken met relaties tussen tabellen. Rij in kolom berekeningen werken altijd met een tabel, maar aggregaat berekeningen werken in de Power gereedschappen vaak over meerdere tabellen. Een year-to-date berekening bijvoorbeeld, maakt dan ook gebruik van meerdere tabellen, die met elkaar verbonden zijn door middel van relaties.

Ik beweer niet dat iedere financial een database specialist moet worden, verre van dat zelfs. Maar de rol van de financial verandert snel. En dat heeft weer te maken met de centrale rol van financiën in de organisatie. Volgende keer kijken we naar de relaties tussen de tabellen en wat dat gaat inhouden voor de moderne financial.

Blogs in deze serie:

• Excel: Tabellen for sale. Maar welke moet ik kiezen?
• Excel: De kunst van rij in kolom berekeningen
• Explosieve kracht in een cel: aggregaat berekeningen
• De liefdesverklaring van Power Pivot: relaties
• De echte waarde van Power BI
• Schema first of data first? Maar waar is je data?
• Visualiseren, de kunst van het verleiden (1)
• Visualiseren, de kunst van het verleiden (2)
• Low-code of no-code, de weg naar de toekomst (1)
• Low-code of no-code, de weg naar de toekomst (2)
• Controles, levensbloed voor je model (1)