Guides

Common Intermediate DAX Patterns in Power BI

By Ravindra Bagale, Power BI & Data Analytics trainer · LinkedIn · Published: 30 September 2026 · Last updated: 30 September 2026

Intermediate DAX is a small set of patterns you can say out loud: base measure, safe ratio, last year, percent of what is on the visual, and “how many cleared the bar”. The function names come after the sentence.

AOV = DIVIDE ( [Total Sales], [Orders] )

One tiny table, five numbers out

Use the same three Pune rows for every pattern so the arithmetic stays visible.

Why a pattern, not a new function each time: the meeting asks five sentences. Each sentence already has a shape.

1. Base measure. Rows in: 3. SUM of Amount out: 200. DISTINCTCOUNT of Order ID out: 2. No FILTER yet. The slicer did the row cutting.
2. Ratio. Why DIVIDE: 200 and 2 are numbers, and orders might be 0 in another city. VAR holds 200 and 2. DIVIDE returns 100. Still no extra FILTER.
3. Last year. Why a date filter: this year's rows are the wrong rows. SAMEPERIODLASTYEAR builds last year's dates. CALCULATE uses that as the filter. Those rows go in, one number comes out. You type FILTER only if you are building that date list by hand.
4. Percent of the cities on the visual. The row's 200 is the numerator. The denominator must see every city still selected, not only Pune. ALLSELECTED is that filter change. DIVIDE returns the share. If you forget it, the percent is 100% on every row because numerator and denominator are the same rows.
5. How many cities cleared a bar. Why FILTER here: the test is the sales measure, so a column filter cannot say it. VALUES lists the cities. FILTER keeps cities above the bar. COUNTROWS returns how many names survived. Rows in, test, one count out.

Read a card only after you can point at which rows went in.

Real example: one Saturday, both cities

Put this on paper before you write five measures.

The Mumbai shop's phone bill this month is 699. The Pune shop's phone bill is 399. Target for a bill: stay at or under 500.

What happens, pattern by pattern:

1. Base. All rows in. Sales out: 1160. Distinct orders out: 4 (M-1 is one order). No extra FILTER.
2. Slice to Mumbai. Rows in: 500, 200, 300. Sales out: 1000. Orders out: 2. AOV out: 500. VAR holds 1000 and 2. DIVIDE makes the average. Still no extra FILTER.
3. Last year. The date filter swaps these slips for last March's slips. Different rows in, one number out. That is not the phone bill.
4. Percent of what you see. Both cities on the matrix: Mumbai 1000 is about 86% of 1160. Pune 160 is the rest. ALLSELECTED lets the denominator see both cities. Forget it, and each row says 100%.
5. Who cleared a bar. "Cities with sales above 500." FILTER keeps Mumbai. COUNTROWS out: 1. "Months with phone bill above 500" is the same shape on the bill table: March Mumbai stays, 399 and 499 drop, count out is 1.

Say the sentence, then point at the rows, then read the card.

What do I need before this guide?

• VAR and RETURN, DIVIDE, last year, percent of total, CALCULATETABLE vs FILTER.
• A marked Date table if pattern 3 is in the page.
• Course: Base measures.

Before and after (look at the tables first)

Pattern 1 — base measures

Total Sales = SUM ( Sales[Amount] )

Orders = DISTINCTCOUNT ( Sales[Order ID] )

1. Everything else calls these. Do not nest SUM inside every new idea.
2. The name is the contract. Orders must not be line count.

Pattern 2 — name it, then divide

AOV =
VAR SalesAmt = [Total Sales]
VAR OrdersN = [Orders]
RETURN
    DIVIDE ( SalesAmt, OrdersN )

1. No slash. Empty cities stay blank instead of breaking the page.
2. Format the measure. DIVIDE returns a ratio, not a format.

Pattern 3 — same period last year

Sales LY =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

AOV vs LY =
VAR NowAOV = [AOV]
VAR LyAOV =
    CALCULATE ( [AOV], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    DIVIDE ( NowAOV - LyAOV, LyAOV )

1. This needs the Date table. If last year is blank, fix the model before you invent a new function.
2. Format AOV vs LY as a percent.

Pattern 4 — percent of what the reader sees

Pct of Cities =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLSELECTED ( Customer[City] ) )
)

1. On a City matrix, rows should add toward 100% of the visible cities.
2. An outer slicer should still bite. That is why this is ALLSELECTED, not a casual ALL.
3. If the business sentence is “percent of every city, ignore the slicer”, you picked the wrong pattern. Say the sentence again.

Pattern 5 — how many cleared the bar

Cities Above =
COUNTROWS (
    FILTER (
        VALUES ( Customer[City] ),
        [Total Sales] > 100000
    )
)

1. The bar is a practice number. Replace it with a real target later.
2. The result is a count of cities, not sales.

One page, five cards

Put them on a single report page:

1. Card: Total Sales.
2. Card: AOV.
3. Card: AOV vs LY.
4. Matrix: City and Pct of Cities.
5. Card: Cities Above.
6. Slicers: Year-Month and, if you want, a region that filters City.

Then ask, out loud, one sentence per card. If you cannot say it, the measure is not finished.

Ghabru naka — you do not need a new function for a new meeting. You need the sentence.

Mistakes and calm fixes