As of this date (Oct 2023), Calculation groups can now be authored directly within Power BI desktop - no Tabular editor required!
How to create calculation groups in Power BI Desktop?
Because it's still in preview (as of this writing), you'll need to first enable it in the options first.
File -> Options -> Global -> Preview features -> Model explorer and Calculation group authoring
1. Once enabled, navigate to the Model view. You'll now see a new tab in the Data pane on the right.
2. Click "Calculation groups" and select "+ New calculation group".
3. The DAX editor will open. You can now begin writing the DAX code.
4. Write the DAX code. I'll create a YTD calculation group for this example.
5. This will now show up in two places; in the calculation group section and in the tables section.
6. To create an additional calculation item, select the calculation item section, and click "+ New calculation item" again.
If you are unsure what Calculation groups are or how to use them, read on below.
What are Calculation groups?
Calculation groups essentially allow you to create "generic" or "cookie cutter" measures. For instance, instead of creating a YTD measure for [Total Sales], [Total Quantity], [Total Purchased], etc... you can create a YTD calculation group that will apply a YTD transformation to your measures (and indeed, any other measure you have).
It's almost like a custom function for DAX, that takes in a parameter (your measure), and applies a function to it. The syntax is as follows (i.e. for a YTD transformation):
CALCULATE(
SELECTEDMEASURE(),
DATESYTD( 'Date Table'[Date])
)In the above example, SELECTEDMEASURE() is a DAX function specially used for calculation groups that allows you to "pass" in the measure you that want to apply the YTD transformation on.
How to Use a calculation group on your dashboard?
Once you have a calculation group...
1. Add a visual (i.e. a matrix) to the canvas.
2. Add the calculation group to the X or Y axis.
3. Add your measure to the Values (i.e. [Total Sales])
And that's how you can create and use calculation groups on your Power BI desktop dashboard! Cheers!
Comments