2.4Introduction to DAX: SUM, AVERAGE, COUNT
The essentials in 30 seconds
- DAX = the calculation language of Power BI (the same as Power Pivot): we start with SUM, AVERAGE, COUNT/COUNTROWS, MIN, MAX.
- DIVIDE systematically replaces "/": armoured division, never again a division-by-zero error.
- CALCULATE evaluates an expression while modifying the filter — "the revenue, but for Paris"; its full mastery occupies level 3.
Running mini-case — Zineb writes her foundation of measures
Zineb, a management controller in an agri-food SME in Algiers, wants three indicators: revenue, the number of sales and the average basket. She writes a foundation of measures: Total Revenue = SUM(Sales[Amount]), Nb Sales = COUNTROWS(Sales), then Average basket = DIVIDE([Total Revenue], [Nb Sales]) — noting that one measure references another. She formats each one (€, thousands separator, decimals). Then she gets comfortable with CALCULATE: Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris"), and Share Paris = DIVIDE([Revenue Paris], [Total Revenue]) formatted in %. Three measures that nest: Zineb is already writing real DAX.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 2.4.
1. The basic aggregations
DAX (Data Analysis Expressions) is the calculation language of Power BI — the same as Power Pivot in Excel. We start with the simple aggregations: SUM, AVERAGE, COUNT/COUNTROWS, MIN, MAX, and DIVIDE — the armoured division that avoids division-by-zero errors. Zineb notes the first good habit: measures are built as bricks, one measure can (must) reference others.
2. An overview of CALCULATE
Then the function that changes everything: CALCULATE, which evaluates an expression while modifying the filter. "The revenue, but only for Paris": Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris"). Its full mastery occupies level 3; here we get comfortable with it on a simple case. Zineb repeats the sentence to herself: "calculate this, with this filter".
3. Naming and formatting
Good habits from day one: name each measure clearly and format it (€, %, decimals) — a report with raw numbers is not finished. We use DIVIDE rather than "/", and we store the measures in a dedicated table. Zineb formats each measure at creation, without exception.
🔎 Concrete example — 🔎 Concrete example — Three measures that nest
Zineb's DAX foundation, from the simplest to the most composed:
Total Revenue = SUM(Sales[Amount])
Sales Count = COUNTROWS(Sales)
Average Basket = DIVIDE([Total Revenue], [Sales Count])
Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris")
Share Paris = DIVIDE([Revenue Paris], [Total Revenue]) // formatted in %Reading: [Average basket] references [Total Revenue] and [Nb Sales]: measures are built as bricks.
Lesson: DIVIDE avoids division by zero; CALCULATE adds a filter without retyping the formula.
Section vocabulary
- DAX
- The language of measures and calculated columns: Excel-like functions, but evaluated in a model context.
Simple example: Zineb writes her first measures in DAX.
What it is not: an Excel formula that only looks at cells.
Reflex: DAX = calculations in contexts.
- SUM / AVERAGE / MIN / MAX
- The basic aggregations on a column: SUM(Sales[Amount]).
Simple example: Zineb calculates Total Revenue = SUM(Sales[Amount]).
What it is not: a sum typed by hand in a visual.
Reflex: a column to aggregate → SUM, AVERAGE, MIN, MAX.
- COUNTROWS vs COUNT
- COUNTROWS(Sales) counts the rows of a table; COUNT counts the non-blank values of a column.
Simple example: Zineb counts her sales with COUNTROWS(Sales).
What it is not: a sum of amounts, which is not a count.
Reflex: count events → COUNTROWS(table).
- DIVIDE
- DIVIDE(numerator, denominator, [if_zero]): armoured division, no more divide-by-zero error.
Simple example: Zineb writes Average basket = DIVIDE([Total Revenue], [Nb Sales]).
What it is not: a division with "/" that can return an error.
Reflex: always DIVIDE rather than "/".
- CALCULATE
- THE function of DAX: evaluates an expression in a modified filter context.
Simple example: Zineb writes Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris").
What it is not: a measure that adds everything up without filtering.
Reflex: "calculate this, with this filter".
In practice — In practice — Your foundation of measures
- Create the base measures: Total Revenue = SUM(Sales[Amount]), Nb Sales = COUNTROWS(Sales), Average basket = DIVIDE([Total Revenue], [Nb Sales]) — note the reuse of measures in a measure, like Zineb.
- Format each measure (Measure tools tab): € for amounts, thousands separator, 0 or 1 decimal.
- First CALCULATE: Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris") — put it in a card next to Total Revenue.
- One more: Share Paris = DIVIDE([Revenue Paris], [Total Revenue]) formatted in % — three measures that nest.
Step-by-step script — write your DAX foundation like Zineb (8 min)
1) New measure: Total Revenue = SUM(Sales[Amount]). 2) New measure: Nb Sales = COUNTROWS(Sales). 3) Average basket = DIVIDE([Total Revenue], [Nb Sales]) — you reuse measures within a measure. 4) Format each measure (€, thousands, decimals). 5) CALCULATE: Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris"), then Share Paris = DIVIDE([Revenue Paris], [Total Revenue]) in %. 6) Store everything in a "_Measures" table.
If you hesitate over a DAX function, the dax.guide reference (SQLBI) documents each one with examples.
Copy-paste template — my foundation of measures
Total Revenue = SUM(Sales[Amount])
Sales Count = COUNTROWS(Sales)
Average Basket = DIVIDE([Total Revenue], [Sales Count])
Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris")
Share Paris = DIVIDE([Revenue Paris], [Total Revenue]) // %
Rules: DIVIDE (never "/"); format each measure; "_Measures" table.Revenue
Zineb writes Total Revenue = SUM(Sales[Amount]): the base measure of her whole dashboard.
Number of sales
Zineb counts her transactions with Nb Sales = COUNTROWS(Sales), more reliable than a COUNT of a column.
Average basket
Average basket = DIVIDE([Total Revenue], [Nb Sales]): a composed measure that builds on two others.
The revenue of Paris
Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris"): the same revenue, but filtered on Paris.
✓ Best practices
- Build the measures as bricks (one measure referencing others).
- DIVIDE rather than "/" systematically.
- Format each measure at creation (€, %, decimals).
✗ Common pitfalls
- Dividing with "/" (risk of a division-by-zero error).
- Leaving raw, unformatted numbers (unfinished report).
- Believing you master CALCULATE straight away (its depth is at level 3).
Key takeaways
- Measures are built as bricks: one measure can (must) reference others.
- DIVIDE systematically replaces "/": division by zero no longer exists.
- CALCULATE = "calculate this, with this filter": the sentence to repeat to yourself.
- Format each measure at creation: a report with raw numbers is not finished.
48h challenge — apply it tomorrow
Compose Zineb's foundation: write Total Revenue = SUM(Sales[Amount]), Nb Sales = COUNTROWS(Sales) and Average basket = DIVIDE([Total Revenue], [Nb Sales]), format each measure, then add Revenue Paris = CALCULATE([Total Revenue], Sales[City] = "Paris"). Store everything in a "_Measures" table.
- I write DAX measures with SUM, AVERAGE and COUNTROWS.
- I use DIVIDE instead of "/" and I format each measure.
- I write a simple CALCULATE that modifies the filter.
Frequently asked questions
DAX looks like Excel formulas: is it the same language?
The syntax looks similar (SUM, AVERAGE, IF exist in both), but the logic differs: Excel calculates cells, DAX calculates in contexts (the visual, the filters). The same measure gives a different result depending on the city of the bar or the month of the column. Your Excel skills help with the syntax; the context logic is learned — that is the leap of level 3.
Where can I learn DAX functions beyond this guide?
The official, free reference is dax.guide (by SQLBI): it documents each function with its syntax and examples. Beyond that, SQLBI (Marco Russo and Alberto Ferrari) is the world reference for DAX, and Microsoft Learn offers structured paths that prepare for the PL-300. Our resources section cites them.
What does the CALCULATE function do in DAX?