Introduction
Calculation Groups in Power BI essentially allow you to apply logic or formatting to a variety of measures at the same time. For example, let’s say you were looking to create a Month-To-Date, Quarter-To-Date, and Year-To-Date view across Revenue, Orders, and Units. This would require you to create a calculation for each of the various date views… 9 measures in just this simple example! Imagine now that you want many more date views and many more measures. Calculation groups allow you to define each of the date views for whichever measure is being used in the Power BI view, providing a much simpler solution.
Calculation groups also allow you to apply other attributes to measures. This week we’re going to use calculation groups for Time Intelligence and Dynamic KPI Selection with Formatting.
Requirements
Preparation
- Download and install Tabular Editor
- Load dataset from Data.World
- Transform dataset
- Filter Year = 2022
- Filter for Dates Before: August 24, 2022
- Note: This is so we can use the datesmtd, datesqtd, and datesytd functions
Time Intelligence
- Create a calculation group to summarize the selected measure for a Month-To-Date, Quarter-To-Date, and Year-To-Date view
- Use MTD DAX formula as reference (HINT: replace standard measure with selectedmeasure())
CALCULATE ( [Total Sales], DATESMTD ( orders[order_date] ) )
- Reference Video: Guy in a Cube – Time Intelligence
KPI Selection with Formatting
- Create a calculation group for KPI selection
- KPIs and formats should be as follows:
- Sales and Profit – “$#,##0”
- Quantity – “#,##0”
- Profit Margin – “0.0%”
- Profit Margin = Total Profit / Total Sales
- Create a measure that will be used as the measure we dynamically populate with the KPI and call it “Selected Measure”
- In your calculation item (each KPI), Refer to this metric using the isselectedmeasure() function to limit the selectedmeasure() function to just the “Selected Measure” you just created
- Reference Video: SQLBI – Selectively Replace Measures
Dataset
This week’s dataset is available here.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd, @KerryKolosko, and @NerdyWithData. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.