Launch Deal · 50% off 1st month ·

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.

  1. 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.
  2. 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)
  1. 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() and ISSELECTEDMEASURE() 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.

See plans

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