Power BI calculation groups help in applying specific calculations to existing DAX measures. For instance, calculation groups can implement time intelligence calculations like year-over-year, year-to-date, and currency conversion. It can also enable the selection of a measure in an existing report through a slicer.
Calculation groups are somewhat straightforward. However, coming up with the right design for a model with calculation groups can be challenging when you create multiple calculation groups (or when you use calculation items in measures).
In this guide, you’ll discover what Power BI calculation groups are, why you should use them, and how to create calculation groups in Power BI.
First…
Power BI calculation groups are features introduced to enable the capabilities of DAX (Data Analysis Expressions) in Power BI. It allows users to define reusable calculations (or groups of calculations), which can be applied across multiple measures within a report.
Calculation groups come in handy in scenarios where you have multiple measures that share similar calculations or logic. Instead of writing and maintaining the same logic separately for each measure, you’ll create a calculation group that embodies these common calculations. This will help the user save time and also ensure consistency and accuracy across the report.
With this formula, you’ll have the CALCULATE function and you’ll be grouping “Total Days Off Work” in 25 days, or under 50 days, or any type of custom grouping you desire.
Since the measure has been created, copy it. Next, Navigate to “External Tool.” After that, go to “Tabular Editor.”
Navigate to “Tables” and right-click and select “Create New.” From the dropdown, select “Calculation Group.”
You can name it Calculation groups. Moving on, you’ll have to create a calculation item. To do that, right-click on the calculation group you’ve just created, and go to “Create New.” Select “Calculation Group” from the dropdown.
You can call this one “Less than 25 Days.” Next, navigate to the Expression Editor section, and paste in the measure you’ve copied.
At this point, you’ll have to create another calculation item and call it “Less than 50 Days.” You’ll use the same measure, but change the grouping criteria from 25 to 50.
The next calculation item is for “Less than 70 Days.” You’ll have to do the same steps and just change the grouping criteria to 70.
Finally, you’ll have to create a calculation item for “Greater than 70 Days.” In the DAX expression, change the grouping criteria to greater than (>) 70.
Click save and refresh it on the Power BI desktop.
Stage 1: Logging in to Power BI
Quarters | Vendors | Market Share |
Q1 | Samsung | 27.69 |
Q1 | Apple | 28.45 |
Q1 | Xiaomi | 11.8 |
Q1 | Huawei | 6.53 |
Q1 | Oppo | 5.3 |
Q1 | Vivo | 4.19 |
Q2 | Samsung | 28.14 |
Q2 | Apple | 27.58 |
Q2 | Xiaomi | 12.62 |
Q2 | Huawei | 6.17 |
Q2 | Oppo | 5.5 |
Q2 | Vivo | 4.21 |
Q3 | Samsung | 28.45 |
Q3 | Apple | 27.71 |
Q3 | Xiaomi | 12.9 |
Q3 | Huawei | 6 |
Q3 | Oppo | 5.29 |
Q3 | Vivo | 4.17 |
Q4 | Samsung | 27.97 |
Q4 | Apple | 27.62 |
Q4 | Xiaomi | 12.68 |
Q4 | Huawei | 5.17 |
Q4 | Oppo | 6.07 |
Q4 | Vivo | 4.66 |
Insights:
To create calculation groups in Power BI, you’ll have to navigate to the “Modeling” tab and select “New Calculation Group” from the ribbon. After that, define groups and measures, and manage them within the Power BI interface.
In a calculation group, the ordinal determines the order of calculation items. It specifies the sequence in which calculation items are evaluated and applied within the group’s logic.
Calculation groups in Power BI allow for dynamic grouping and application of measures, and that enhances analysis by enabling users to switch calculations (or perspectives) seamlessly within reports.
Power BI calculation groups enable dynamic switching between different calculation logic for measures within a report, and that enhances flexibility and analysis.
Calculation groups help accommodate diverse business requirements without cluttering the model with redundant measures. It helps users to easily handle an increasing number of calculations. Aside from that, the calculation group offers a structured approach to organizing and managing calculations, and that ensures scalability as reporting requirements evolve over time.
The calculation group promotes consistency in reporting. With consistent reporting, you’re sure of having the same logic applied uniformly across measures. Consistency is necessary for accurate analysis and decision-making, and that comes in handy during enterprise-level reporting where standardized calculations are needed to help align various stakeholders and departments.
To create a calculation group, you’ll have to use Power BI Desktop’s “Modelling” tab, select “New Calculation Group,” and define calculation items and expressions.