2.7 — Statistical Functions, Quick Measures and Calculation Groups
PL-300 Domain 2 · Skill 2.7
What You Need to Know
Basic statistical functions
| Function | Returns |
|---|---|
AVERAGE, AVERAGEX |
Mean |
MEDIAN, MEDIANX |
Middle value, robust to outliers |
MIN, MAX, MINX, MAXX |
Smallest and largest |
STDEV.P / STDEV.S |
Standard deviation of a population / a sample |
VAR.P / VAR.S |
Variance |
PERCENTILE.INC, PERCENTILE.EXC |
The k-th percentile |
RANKX(table, expr) |
Rank of the current item by an expression |
COUNTROWS, DISTINCTCOUNT |
Counts (lesson 2.5) |
Rank products by sales, keeping the rank stable when a product is filtered out of the visual:
Product Rank = RANKX(ALL(Product[Product Name]), [Total Sales])
RANKX iterates the table you give it. Without ALL, each row of the visual only "sees" itself and every product ranks 1. Window functions such as RANK, ROWNUMBER and TOPN are also available.
Quick measures
Quick measures (Home > Quick measure, or right-click a field) generate DAX from a template: running total, average per category, filtered value, year-over-year change, star rating, concatenated list of values and more.
- The result is a normal measure whose DAX you can read and edit, which makes it a good way to learn patterns.
- The time intelligence quick measures need a date table or a date hierarchy.
- With Copilot enabled, you can describe a calculation in words and get suggested DAX.
Calculation groups
A calculation group applies one set of logic to any measure. Instead of writing Sales YTD, Profit YTD, Margin YTD, Sales PY, Profit PY … you write each pattern once.
- In Model view, choose Calculation group. Power BI creates a table with one column (rename it, for example Time Calculation) and turns on Discourage implicit measures.
- Add calculation items, each using
SELECTEDMEASURE():
Current = SELECTEDMEASURE()
YTD = CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))
PY = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))
YoY % = VAR PY = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE(SELECTEDMEASURE() - PY, PY)
- Put the calculation group column on a matrix's columns or in a slicer. Every explicit measure in the visual is shown as Current, YTD, PY and YoY %.
Details worth knowing:
- Calculation groups work only with explicit measures, not implicit column aggregations.
- A format string expression per item fixes formats, for example a percentage for YoY % while the base measure is currency.
- Ordinal controls the order of the items. Precedence decides which group is applied first when there are several.
SELECTEDMEASURENAME()andISSELECTEDMEASURE()let an item treat some measures differently.
Key Concept: Quick measures generate DAX for one measure at a time. Calculation groups apply the same time intelligence or formatting logic to every measure, which removes dozens of near-duplicate measures from the model.
Exam Traps
- Ranking with
RANKX(Product, …)filtered by the visual and getting 1 for every row - Expecting a calculation group to work with dragged-in column aggregations (only explicit measures)
- Writing a separate YTD, PY and YoY measure for every base measure when a calculation group is asked for
- Forgetting a format string expression, so YoY % shows as currency
- Using STDEV.S for a full population (use STDEV.P)
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Use quick measures for common calculations, Microsoft Learn
- Create calculation groups, Microsoft Learn
- Statistical functions (DAX), Microsoft Learn
- RANKX function (DAX), Microsoft Learn