Calculation groups in Power BI

What are calculations Groups?

Calculation groups are a collection of items, which are basically the same measures that you create in your report but are created in a slightly different way. Calculation groups are created in Tabular Editor, which you can download for free from the internet. You can see the Tabular Editor in your External Tools.   This definition was taken from the Power BI community.

In a nutshell for example, if you have Sales, Quantity, Revenue and Cost, and you normally need to add 4 – 5 measures to calculation MTD for each.  By using Calculation groups you will only have to create 1 measure and apply it to all.

What are the benefits?

Reduces the number of measures that one must create.

What you need to do.

Lets’ dive right into how to create calculation groups, that was the only way I could figure out what they are and how great they are.

For Calculation Groups you will only need Tabular Editor, but I’ll show you which ones are good to have.  They are  Tabular editor, DAX Studio and ALM Toolkit.

First things first, need to check if you have the external tools tab on your ribbon.

If not, you’ll need to do the following.  Download tabular editor, DAX studio and ALM toolkit.

https://tabulareditor.com/downloads

https://daxstudio.org/downloads/

http://alm-toolkit.com/

After you have installed it, you just close Power BI, open it up again and they will automatically be there.  Just an FYI, it needs to be the correct versions, otherwise they don’t appear.

Ready to start creating Calculation Groups in Power BI

Open Tabular editor, you will see it is automatically connected to your Power BI model.

Add a table by right clicking and choosing add Calculation group.

Then add a Calculation Item by right clicking on Calculation Item and add.  Rename to appropriate name. My example is MTD.

Double click on Calculation Item, and a DAX screen will open.  Now you will create the MTD measure as you usually would in DAX except where you would add the “Sales” measure as the DAX Measure to use, you will put in SELECTEDMEASURE().  This is essentially a place holder for any measure you choose.

Once created, save it and go to your Power BI, hit the refresh, and your new table will appear.

Now all you need to do is use in on the report canvas in a visual of your choice.  In my example I also added the normal Sales measure, in order to have it in the visual as well.  This was just SELECTEDMEASURE().   You can now use any measure, like Sales, Quantity, Profit, Revenue etc.

I the above video you can see how I use it.  If you do not want to show both Calculation Items in the visual, you can just filter in out in the filter pane, of give the user to choose using a slicer.

You may not see the benefit yet, but as we all know reports can get very robust very quickly, and knowing this going in, will definitely reduce you time when creating measure upon measure.

Data Bear also offer great training program, visit our training page.