Deze blog geeft een inkijk in het maken en gebruiken van calculation groups met Tabular Editor. Het grote voordeel van het gebruik van calculation groups is dat je de hoeveelheid benodigde measures in een rapport significant kunt beperken. De meest bekende toepassing van calculation groups is te vinden bij tijdsintelligentie, er zijn echter veel meer nuttige toepassingen waar je op het eerste gezicht niet direct denkt. In deze blog gaan we hier dieper op in. We kijken eerst wat calculation groups zijn en hoe je ze maakt. Vervolgens beschrijven we 5 situaties waarbij calculation groups nuttig kunnen zijn. Er zijn zeker nog meer toepassingen, maar dat is voor een andere keer.

 

Wat is een calculation group en hoe maak je ze?

 

Een calculation group is een verzameling van ‘calculation items’, deze items zijn ‘een soort measures’ die heel veel op elkaar lijken maar die in een rapport net anders gebruikt worden. Om de bouw- en onderhoudswerkzaamheden te beperken is het gebruik van een calculation group dan heel handig. Calculation groups kunnen worden gemaakt in Tabular Editor. Tabular Editor is een (hulp)programma voor Power BI (gratis te installeren) en vervolgens onder andere bereikbaar is via Power BI onder ‘Externe hulpprogramma’s:

 

 

Nadat je tabular editor hebt geïnstalleerd kan je een calculation group maken. Dit gaat eenvoudig door met je rechtmuisknop op Tables te klikken > Create New > Calculation Group:

 

 

Vervolgens maak je een calculation item aan. Calculation items zijn als het ware measures binnen een calculation group. Echter zijn ze net iets anders dan je gewend bent. Met de rechtermuisknop op calculation group ga je naar Create New > Calculation Item.

 

 

In de Expression Editor wordt een calculation item opgesteld. Eén DAX functie is hierbij belangrijk om te onthouden, namelijk SELECTEDMEASURE(). Met deze expressie haal je de measure op die je in de visual wilt gebruiken. Dit werkt dynamisch en het kan dus elke measure zijn. Hieronder zie je een voorbeeld waarbij de SELECTEDMEASURE() gefilterd wordt op DATESYTD:

 

 

Er zijn vele mogelijkheden met calculation groups, hieronder zullen we onze top 5 toelichten:

1. 1. Tijdsintelligentie
   2. Switch measures
   3. Dynamische format strings
   4. Categorieën uitbreiden met custom measures
   5. Dynamisch relaties aanpassen

 

 

1. Tijdsintelligentie

 

Calculation groups en tijdsintelligentie worden al snel met elkaar geassocieerd. Neem als voorbeeld een omzetrapportage waarin we de huidige omzet willen vergelijken met dezelfde periode vorig jaar. Behalve omzet worden er vaak nog veel meer berekeningen gemaakt zoals Net Sales, COGS, Margins etc. Stel je hebt 10 van deze measures en vervolgens wil je de YTD, QTD en SPLY (same-period-last-year) cijfers hiervan zien. Dat betekent dat je in dit geval al snel zo’n 30 measures moet maken. Een calculation group gaat je in deze situatie zeker helpen.

 

 

In de bovenstaande afbeelding zie je de calculation group Time Intelligence. Daaronder zie je drie calculation Items (YTD, QTD en SPLY). Deze calculation group kan je gebruiken als slicer. Daarnaast is het ook mogelijk meerdere calculation groups aan te maken waarmee je gaat filteren. Denk bijvoorbeeld aan de selectie van een bepaalde metric (bijvoorbeeld: Sales Amount, Sales Quantity of Total Costs). In het voorbeeld hieronder bepaal je welke soort tijdsintelligentie je gebruikt, maar ook welke waarde er moet worden berekend via de Metric calculation group:

 

 

Naast het gebruik voor een slicer kunnen calculation groups ook direct in een visual gebruikt worden. Hiermee worden dan alle calculation items getoond. Mocht je hier meer over willen weten, dan heeft SQLBI een uitgebreid artikel geschreven: Introducing Calculation Groups.

 

 

2. Switch measures

 

Een SWITCH measure is eigenlijk een groot IF statement. Bij het selecteren van een bepaalde waarde doet de measure een berekening aan de hand van de logica in de SWITCH measure. Deze geselecteerde waarde staat vaak in een losse tabel en wordt geselecteerd via een slicer in het rapport. Een groot nadeel waar je in dit geval rekening mee moet houden is dat er een beperking zit op het gebied van formatting. De uiteindelijke measure kan maar één format hebben. Als je bijvoorbeeld 3 measures (Geldeenheid, Integer, Percentage) hebt die verschillende formats nodig hebben, kan een grafiek deze wel tonen zoals je wilt, maar de as zal zich niet aanpassen als je een andere measure selecteert.

 

Met calculation groups kun je het juiste format meegeven in Tabular Editor. Dit is te vinden onder custom format strings. Let op, de notatie van de format strings is heel specifiek. Bas van How to Power BI heeft hier een handige video over gemaakt op youtube: CUSTOM FORMAT STRINGS in Power BI | Making sense of ##0,.0 K.

 

Binnen een calculation group maak je de calculation items aan. Voor deze items zet je de juiste formatting string. Zodra je dat hebt gedaan, maak je nog een dummy measure aan waarmee je naar die items refereert. Hiermee maak je de formatting van de grafiek-as dynamisch. Hieronder zie je die dummy measure als “Selected Chart Item”.

 

 

De calculation items worden in een slicer gebruikt om de selectie te bepalen, en de ‘Selected Chart Item’ helpt met de juiste formatting voor de as. Nu is de visual dynamisch en geeft het de juiste formatting aan de berekeningen.

 

 

 

3. Dynamische format strings

 

We kunnen nog een stap verder gaan met format strings. Deze kunnen namelijk ook dynamisch opgezet worden voor elk calculation item. Dit is erg krachtig als je als gebruiker bijvoorbeeld verschillende valuta’s wilt kunnen tonen of switchen naar percentages. In de Expression Editor is de mogelijkheid om de Format String Expression aan te passen. In de afbeelding hieronder wordt duidelijk dat je bij Property naast “Expression” ook “Format String Expression” kunt selecteren. Dit is als het ware de metadata van het calculation item.

 

Hieronder een voorbeeld waarbij je, op basis van een selectie in de rapportage, de formattering kunt aanpassen. In dit scenario is er één measure, “Volume (tonnes)”, die altijd dezelfde formattering moet houden. Dit is gedaan omdat die measure wel in absolute waarden moest blijven (en bijvoorbeeld niet in duizendtallen of percentages). De andere ‘metrics’ moeten dynamisch aan te passen zijn via een slicer. Deze slicer is mogelijk via een losgekoppelde ‘metrics’ tabel.

 

 

De onderstaande calculation items kunnen nu worden gebruikt in de visual. Op basis van een slicer op ‘Metrics'[Format] kan worden bepaald hoe de formatting wordt getoond.

 

 

Hieronder het resultaat in het Power BI rapport. Op deze manier, met gebruik van calculation groups, is het mogelijk te switchen tussen verschillende measures en kan het format bepaald worden via de metrics slicer.

 

 

 

4. Categorieën uitbreiden met custom measures

 

Vaak zien we dat er een wens is om extra inzichten te tonen door bijvoorbeeld naast ‘de sales per jaar’ in kolommen te tonen, ook ‘extra handmatig berekende kolommen’ toe te voegen. Zie hieronder.

 

 

Het is mogelijk om dit met losse measures op te lossen, dan mis je echter de mogelijkheid om een selectie van de beschikbare kolommen te selecteren. Door gebruik te maken van een calculation group, kan deze volledig gebruikt worden als een filter om de zichtbaarheid van kolommen te wijzigen:

Voor een gedetailleerdere uitleg raden we deze blog van Matt Allington aan: Building a Matrix with Asymmetrical Columns and Rows in Power BI.

 

 

5. Dynamisch relaties aanpassen

 

Het komt vaak voor dat er meerdere datumvelden worden gebruikt in een datamodel. Denk aan Order Date, Delivery Date, Ship Date etc. Via de datum-dimensie heb je een actieve relatie liggen op de meest gebruikte datum en eventueel inactieve relaties op de andere datumvelden. Als alternatief is het ook mogelijk om meerdere datum-dimensies te gebruiken. De complexiteit van het datamodel wordt daarmee wel groter, hieronder beschrijven we een alternatieve oplossing met het gebruik van de USERELATIONSHIP functie.

 

Door het gebruik van USERELATIONSHIP als filter in de CALCULATE functie kan je een relatie voor een specifieke measure actief maken. Calculation groups helpen je om dit dynamisch te maken met een slicer zodat je ook meerdere measures via die relatie kan laten lopen. Hieronder zie je de expressie die gebruikt wordt in het calculation item waarbij een andere relatie wordt geactiveerd:

 

 

Vergelijkbaar met relaties actief maken zoals hierboven genoemd, kan de wens ook bestaan ‘om de richting van de relatie aan te passen’. Hiervoor wordt de CROSSFILTER functie gebruikt. Dit kan met measures, beter is nog om dit dynamisch te regelen met een calculation group en een slicer. Hieronder een voorbeeld van een CROSSFILTER expressie in het calculation item:

 

 

 

Samenvattend

 

In deze blog hebben we beschreven wat calculation groups zijn en hoe je deze via Tabular Editor kunt maken. Verder hebben we laten zien dat er talloze toepassingen zijn als het gaat om tijdsintelligentie. Vervolgens zijn we de measure formats ingedoken waarbij niet alleen de as van een grafiek dynamisch wordt, maar dat het zelfs mogelijk is om via een slicer het format voor de geselecteerde measure aan te passen. We hebben gezien dat het mogelijk is om categorieën uit te breiden met custom measures en hoe je voor meerdere measures de relaties aan kunt passen.

 

Hiermee hebben we wat ons betreft ‘de Top 5 toepassingen van calculation groups’ aangeraakt, maar er zijn nog veel meer handige toepassingen in het dynamisch maken van rapportages en visualisaties. Enkele blogs voor meer deep-dive informatie:

• Avoiding Pitfalls in Calculation Groups Precedence
• Understanding the interactions between composite models and calculation groups

 

Hopelijk geeft deze blog je inzicht in de mogelijkheden van calculation groups en kun je er nu zelf mee aan de slag. Heb je na deze blog nog vragen, over calculation groups of Power BI in het algemeen, neem dan gerust contact met ons op!