Statistical Functions and Calculation Groups

What it is

DAX includes basic statistical functions for averages, spread and ranking. When you have many measures that need the same variation, such as year to date or prior year for every measure, a calculation group applies one set of calculation items to all of them. That avoids writing a separate measure for every combination.

How it works

Basic statistical functions

For the values 2, 4, 4, 4, 5, 5, 7 and 9, the average is 5, the median is 4.5 (the middle two values are 4 and 5), the population variance is 4 and the population standard deviation is 2. The S versions divide by n minus 1 and so give a larger result for the same data. Use .P when the column is the whole population, and .S when it is a sample.

Median Order Value = MEDIAN ( Sales[Amount] )
Product Rank = RANKX ( ALL ( Product[Name] ), [Total Sales] )
Top 3 Sales = CALCULATE ( [Total Sales], TOPN ( 3, ALL ( Product[Name] ), [Total Sales] ) )

RANKX needs ALL (or a similar table) so that the ranking covers every product, not only the one in the current filter context.

What a calculation group is

A calculation group is a special table with one column. Each row of the column is a calculation item, a DAX expression that modifies whatever measure is in use. The placeholder SELECTEDMEASURE() stands for that measure.

With three measures and four items you get twelve results from seven definitions.

Creating a calculation group

1. Open Model view, and select Calculation group in the ribbon (the table appears in the Model explorer).
2. If Discourage implicit measures is off, Power BI asks you to turn it on. Accept, because calculation items apply only to explicit measures.
3. Edit the first calculation item, then add more with New calculation item.
4. Rename the group and its column. The column is what you put on matrix columns or in a slicer.
5. Optionally set a dynamic format string on an item, such as a percentage format for YoY %, and set the precedence if the model has several groups.

You can also build one in TMDL view. Use the calculation group column on matrix columns to show each item side by side, or in a slicer to apply one item to every measure.

Considerations

• Calculation items affect explicit measures only, not implicit ones.
• Once a calculation group exists, measures use the variant data type in reports.
• A calculation item that does math on a text measure, such as a title measure, can show an error. Protect it with ISNUMERIC ( SELECTEDMEASURE () ).
• With several calculation groups, precedence decides which is applied first.

A worked scenario

A finance model has Sales, Cost and Margin measures, and users also want YTD, prior year and year over year for each. Instead of writing twelve measures, the analyst creates a calculation group named Time Calculation with items Current, YTD, PY and YoY %, and a dynamic format string for YoY % so it shows as a percentage. In a matrix she places the Time Calculation column on columns and the three measures in values. Adding a fourth measure later needs no extra items.