Calculation Groups and Field Parameters

[Ilgar_Zarbali]

You’re likely already familiar with the AdventureWorks dataset, but I’ll provide a bit more detail. It contains tables such as Product, Region, Reseller, Salesperson, and Sales. The Calendar table is constructed using the minimum and maximum dates from the Sales table.

The KeyMeasures group contains four basic measures: Total Sales, Total Costs, Total Quantities, and Total Transactions.

• Total Sales calculates the sum of all sales amounts.
• Total Costs calculates the sum of all costs.
• Total Quantities calculates the sum of all quantities sold.
• Total Transactions counts the total number of rows.

Let’s create a matrix visual to display my total costs, total quantities, total transactions, and total sales by year and month. The matrix will appear as follows:

Let’s imagine our Manager wants us to include additional metrics such as Average Costs per Reseller, Cumulative Costs YTD, and Monthly Cost Share for the Year (%). We can create measures for these metrics and add them to the matrix visual.

Average Cost per Reseller

Average Cost per Reseller =
AVERAGEX(
Reseller,
[Total Costs]
)

Cumulative Costs YTD

Costs YTD =
TOTALYTD(
[Total Costs],
'Calendar'[Dates]
)

Monthly Cost Share for the Year (%)

Monthly cost share for the year (%) =
DIVIDE(
[Total Costs],
CALCULATE(
[Total Costs],
REMOVEFILTERS('Calendar'[Month Name], 'Calendar'[Month])
)
)

In the last measure, you could use ALL or ALLSELECTED instead of REMOVEFILTERS, but I’ll proceed with REMOVEFILTERS here.

Note: Both 'Calendar'[Month Name] and 'Calendar'[Month] are referenced because Month Name is sorted by the numeric Month column.

Let’s duplicate the current Matrix visual, remove the existing measures, and replace them with the newly created ones. The matrix will appear as follows:

If our Manager requests the same calculations for total sales, quantities, and transactions, we would need to create new measures for each, which would be repetitive and time-consuming. This is where calculation groups can help. To create calculation groups, we navigate to the Model view in our Power BI Desktop file. The window will appear as follows:

Click on the Calculation Group button, and the following window will appear:

If the Discourage implicit measures property is turned off, you’ll be prompted with a dialog box to enable it before creating a calculation group.

An implicit measure is created when you drag a data column from the Data pane directly into a visual in Report view and choose a basic aggregation such as SUM, AVERAGE, MIN, or MAX. Creating a calculation group prevents this by hiding the summation icon next to data columns in the Data pane and blocking columns from being added directly to a visual’s aggregation axis or values.

Any implicit measures already used in visuals will still work. However, this property must be enabled because calculation items only work with explicit measures—not implicit ones.

After selecting Yes, or if the Discourage implicit measures property is already enabled, a calculation group will be created, allowing you to define the DAX expression for the first calculation item in the DAX formula bar.

The SELECTEDMEASURE() DAX function serves as a placeholder for the measure that the calculation item will be applied to.

Let’s rename the Calculation Group to Insights and change the Calculation Group Column name to Metrics.

Click the ellipsis next to Calculation Item 1 and select New Calculation Item.

Here, I’m adding the three DAX measures I created for the cost-based calculations. I double-click on the newly added calculation item and enter the following measure:

Averages per Reseller =
AVERAGEX(
Reseller,
[Total Costs]
)

Here, instead of using the Total Costs measure, I replace it with SELECTEDMEASURE.

Year-To-Date =
TOTALYTD(
[Total Costs],
'Calendar'[Dates]
)

Here, instead of Total Costs, I will use SELECTEDMEASURE.

Shares for the year (%) =
DIVIDE(
[Total Costs],
CALCULATE(
[Total Costs],
REMOVEFILTERS(
'Calendar'[Month Name],
'Calendar'[Month]
)
)
)

Again, instead of Total Costs, I will use SELECTEDMEASURE.

In the Shares for the Year (%) measure, the output should be displayed as a percentage. To achieve this, we’ll adjust the column properties. While this calculation item is selected, expand the Properties pane and switch Dynamic format string to Yes. In the format string field, enter "0.00%". 

Return to the Report view of the file and duplicate the Manual page. Rename the new page to CalculationGroup. Remove one of the matrix visuals and clear all the measures from the remaining matrix. The Insights calculation group has now appeared in the file’s Data pane.

Add Metrics from the Insights group to the Columns section of the Matrix visual. The following window will be displayed:

Now, add the Total Costs measure to the selected Matrix. It will appear as follows:

We can switch between different measures, and each selection will update the values in the Matrix accordingly. Using Field Parameters, we can make this process more interactive. To do this, go to the Modeling tab and select New Parameter → Fields.

In the Name field, enter SelectedMeasure, and in the Fields section, add the following measures:

• TotalCosts
• TotalSales
• TotalQty

Make sure the Add slicer to this page option is checked.

Change the slicer’s format to the Tile layout.

To make the Matrix visual interactive, remove TotalCosts from the Values section and add the new SelectedMeasure parameter instead. As you switch between measures, the values in the Matrix will update accordingly.

Thank you for exploring our latest newsletter, "Calculation Groups and Field Parameters." We hope this guide has shown you how easily you can enhance your reports with flexible axis selection, dynamic measure switching, and customized column formatting—all with just a few clicks and DAX formulas. Stay tuned for future editions, where we’ll reveal more powerful features, present real-world use cases, and help you elevate your Power BI storytelling. Don’t miss out—subscribe now and keep transforming the way you deliver insights!
LinkedIn