Excel: de kunst van rij in kolom berekeningen

Een serie blogs over Excel en Power BI.

BLOG – In mijn vorige blog heb ik uitgelegd hoe tabellen werken. Nu neem ik je mee in de berekeningen. Vergelijk je berekeningen in Excel met die in Power Pivot of Power BI, dan lijken die hetzelfde. Maar schijn bedriegt: ze verschillen hemelsbreed.

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

Berekeningen vallen uiteen in twee smaken: rijberekeningen in een kolom (lekker verwarrende omschrijving, vind je ook niet?) en aggregaat berekeningen. Met een rijberekening in een kolom voeg je een kolom aan de tabel (of Excel bereik) toe, waarna je de berekenende informatie op rij niveau aanbrengt. Een aggregaat berekening berekent de (totale) waarden uit een kolom of tabel. Tel je bijvoorbeeld een kolom op met de SOM functie dan is het resultaat één getal, die geen onderdeel uitmaakt van een kolom of tabel. In deze blog neem ik je mee naar algemene principes van berekeningen en de rijberekeningen in een kolom. In de volgende blog ga ik verder in op de werking van aggregaat berekeningen. 

Power Pivot of Power BI maakt gebruik van DAX (Dynamic Analyses eXpression) berekeningen en wordt in principe altijd gevisualiseerd in de vorm van een draaitabel of -grafiek. Essentieel hierbij is het besef dat een berekening in een draaitabel los staat van de visualisatie. De berekening gebruik je om te filteren over de rij- en kolom gegevens. En daarmee raak je precies de kern van het verschil in werken tussen Excel en Power Pivot. De basis van het werken met Power Pivot en Power BI is filteren, terwijl de basis van Excel, niet vreemd denk ik zo, bestaat uit berekeningen.

De logische conclusie is dat een draaitabel niet tot het domein van de berekening behoort, maar in de vierde laag: de visualisatie. En nu hoor ik je al brommen: “Maar ik selecteer ook een berekening in mijn Excel draaitabel”. Dat klopt, maar dat komt alleen door de manier waarop klassieke Excel draaitabellen werken.

In afbeelding 2 zie je een Excel draaitabel. Hier is gekozen om de som te gebruiken van de kolom Waarde. De klassieke Excel draaitabel kent 11 ingebouwde berekeningen. Deze aggregaat berekeningen gebruik je om vervolgens te filteren, in dit geval over de afdeling. Kijk je naar afbeelding 3, dan zie je dat je in Power Pivot eerst een aggregaat berekening maakt, die de som van de kolom Waarde berekent. Deze berekening gebruik je in de draaitabel (afbeelding 4), waarna je dezelfde filtering op afdeling toevoegt. 

In dit type draaitabel is de berekening gescheiden van de draaitabel. Het subtiele, maar indrukwekkende, verschil is dat je iedere aggregaat DAX berekening kan gebruiken in een Power Pivot of Power BI draaitabel. Ongeveer een oneindige plus één hoeveelheid soorten DAX berekeningen tegen de standaard 11 Excel berekeningen. Helaas, de wereld is ongelijk verdeeld. Maar misschien komt mijn ‘onpartijdige’ voorkeur voor een bepaald type draaitabel hier wel enigszins naar voren.

Terug naar de rijberekeningen in een kolom. Kijk eens mee naar de Excel functie =YEAR() in afbeelding 5 en de Power Pivot DAX functie = YEAR() in afbeelding 6. Ogenschijnlijk zien beide berekeningen er hetzelfde uit. Er is wel degelijk een verschil: let maar op de verwijzingen tussen de haakjes open en de haakjes sluiten. Je ziet bij de Excel functie een verwijzing naar cel A2: een kruispunt tussen een kolom en rij en daarmee een specifiek punt in de tabel. In de DAX functie is dit een verwijzing naar een kolom (Table1[Datum]), zonder rij aanduiding. Hiermee wordt meerdere, gelijkwaardige punten in de tabel aangeduid. Dit schijnbaar kleine verschil maakt een wereld van verschil.

Laten we iets dieper duiken in de verschillen tussen de rij in kolom berekeningen in Excel en Power Pivot of Power BI. In de klassieke manier van werken in Excel maak je voor iedere cel in de kolom van de tabel een berekening: is je tabel 5.000 rijen lang, dan maak je even zoveel berekeningen. Een van de meest voorkomende problemen van deze methode is wanneer de tabel meer rijen krijgt, bijvoorbeeld omdat je meer transacties toevoegt. Omdat Excel functies geen flauw benul hebben van de (kolom in de) tabel, worden formules niet automatisch toegevoegd. Dit soort problemen heeft Excel, overigens geheel ten onrechte, een onbetrouwbaar imago gegeven. Berekeningen in Excel op zichzelf zijn goed – ik heb Excel nog nooit op een rekenfoutje betrapt – maar is het de onkunde van de gebruiker, die zorgt voor onbetrouwbare resultaten. Hij of zij vergeet bijvoorbeeld de berekeningen aan te vullen, wanneer de tabel langer is geworden, of voegt extra berekeningen toe aan een individuele formule in de kolom.

Goed, nu de kant van de DAX berekeningen. De berekening gaat over een kolom, vandaar de naam ‘Berekende kolom’. In de formule zie je eerst de verwijzing naar een tabel. Dat betekent dat de DAX functie kennis heeft van de tabel! Groeit de tabel door toegevoegde transacties, dan wordt de berekening automatisch verlengt tot het einde van de kolom. Omdat een DAX functie geen kennis heeft van een cel positie, is het onmogelijk om een specifiek positie te voorzien van een aangepaste berekening. Deze eigenschappen werken gunstig op een consistente opbouw van jouw model.

Traditionele Excel rapportages worden vaak gebouwd in de uitvoer. Helaas is het ombouwen van dit soort rapportages naar Power Pivot of Power BI al snel een brug te ver. Een van de problemen is dat de kolom berekeningen in de uitvoertabel worden gecombineerd met aggregaat berekeningen, waardoor een hybride tabel ontstaat. En een hybride tabel, dat staat Power Pivot of Power BI niet toe. Een voorbeeld hiervan is de berekening van subtotalen. Subtotalen mag en kan je zeker gebruiken, zolang je de rij in kolom en de aggregaat berekeningen gescheiden houdt. Het betekent wel dat je dit soort rapportages van de grond af aan opnieuw moet definiëren en bouwen.

De vraag is niet of je dit soort Excel rapportages moet ombouwen, want dat moet je eigenlijk wel doen. Maar het houdt wel in dat jij moet weten hoe tabellen en berekeningen werken. En daar komt bijna zeker training om de hoek kijken! 

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)
• 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)