Ravindra BagaleCourses & study guides मराठी Track your progress

32. Keyboard Shortcuts and DAX Cheat Sheet

32.3 Common Measure Patterns (Copy & Adapt)

By Last updated: 

Total Orders    = DISTINCTCOUNT(Orders[Order ID])
AOV             = DIVIDE([Total Sales], [Delivered Orders])
Cancel Rate     = DIVIDE([Cancelled Orders], [Total Orders])
Avg Deliv Mins  = AVERAGEX(VALUES(Orders[Order ID]),
                      CALCULATE(MAX(Orders[Delivery Time Mins])))
% Under 10 Mins = DIVIDE(CALCULATE([Delivered Orders],
                      Orders[Delivery Time Mins] <= 10), [Delivered Orders])
% of Total      = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)))
Sales LY        = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY %           = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Sales YTD       = TOTALYTD([Total Sales], 'Date'[Date])
Running Total   = CALCULATE([Total Sales],
                      FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date])))
Rolling 12M     = CALCULATE([Total Sales],
                      DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH))
Rank            = RANKX(ALL(Product[Product Name]), [Total Sales], , DESC, DENSE)
Top 5 Sales     = CALCULATE([Total Sales], TOPN(5, ALL(Product[Product Name]), [Total Sales]))
Selected Item   = SELECTEDVALUE(DarkStore[City], "All Cities")
Delivered Sales = CALCULATE([Total Sales],
                      USERELATIONSHIP(Orders[Delivered Date], 'Date'[Date]))

Ravindra Bagale's Tip

Friends, many students copy a measure pattern and forget to change the table and column names to match their model. After pasting, read every reference and replace it, then test the measure with a slicer before using it in the report. This matters for both exams and interviews.

Thodkyaat sangaycha tar (quick recap)

Make a habit of the few shortcuts you use every day, and after copying a pattern, change the table and column names to match your model. Finally, the glossary.

Deepen this topic (Why / What / How)

Fictional Maharashtra practice. Shortcut lists can differ by Desktop version — verify in your build.

Why: Blank-page syndrome wastes time when the pattern already exists.

What: Copy-adapt patterns: totals, ratios, YTD, previous period, % of total — always on a Date table + star schema.

How — adapt patterns safely:

Vertical steps (one under another)

  1. Copy a pattern into a measures table — rename to business words.
  2. Replace table/column names to your model (FactOrders, DimDate).
  3. Verify against a known Excel total for one city/day (fictional data OK).
  4. Avoid copy-paste of time intel without Mark as Date table.
  5. Advance with Ch 47 time intel and Ch 48 calc groups / field parameters.

Tiny example: Fictional: [Orders YTD] using datesYTD on DimDate[Date] after marking Date table.

Ravindra Bagale's Tip

Friends, adapt means change names and test — not paste blindly into production.

Sample shortcut habit ladder
Sample layout — fictional habit ladder

Core patterns to keep on a card

Vertical points

  1. Base total: [Delivered Orders] = COUNTROWS(FactOrders) or SUM of units.
  2. Safe ratio: [AOV] = DIVIDE([Revenue],[Delivered Orders]).
  3. % of total: DIVIDE([Revenue], CALCULATE([Revenue], ALL(DimCity))).
  4. Previous period: CALCULATE([Revenue], DATEADD(DimDate[Date], -1, MONTH)) — needs Date table.
  5. Status flag measure for conditional formatting (IF threshold).

When patterns break

Vertical points

  1. Wrong grain, then totals explode; revisit relationships (Ch 11).
  2. Missing Date table, then time intel lies.
  3. Implicit measures hide in visuals — prefer explicit measures.
  4. Performance: heavy iterators on large facts — see Ch 49.
  5. Interview: explain pattern in Why/What/How, not only paste DAX.

Go deeper next

Use this chapter for speed. For DAX mastery open Ch 45–Ch 48; performance Ch 49.

Worked pattern board

Why: Interviews ask for % of total and previous period constantly.

What: Two patterns with easy English commentary.

How — copy, rename, test:

Vertical steps

  1. Create [% of City Revenue] using DIVIDE + CALCULATE + ALL(DimCity).
  2. Create [Revenue PM] with DATEADD -1 MONTH on DimDate[Date].
  3. Put both on a matrix: City × Month.
  4. Explain aloud: filter context removed for city total; date shifted for PM.
  5. If numbers look wild, check relationships and Date table mark first.
[% of City Revenue] =
DIVIDE (
    [Revenue],
    CALCULATE ( [Revenue], ALL ( DimCity ) )
)

[Revenue PM] =
CALCULATE (
    [Revenue],
    DATEADD ( DimDate[Date], -1, MONTH )
)

Ravindra Bagale's Tip

Friends, if you cannot explain ALL() in one sentence, do not ship the % measure yet.

Quiz (5 questions)

1. Adapt a pattern means:

2. % of total often uses:

3. Time intel patterns deepen in:

4. Patterns break when grain is wrong:

5. Prefer measures that are:

Practice tasks (12) — hints only, no full answers

  1. Task. Adapt a YTD pattern on a marked Date table.
    Hint: Verify month totals.
  2. Task. Build % of total by City.
    Hint: ALL(DimCity).
  3. Task. Build previous month revenue pattern.
    Hint: DATEADD -1 MONTH.
  4. Task. Create a measures table and move patterns there.
    Hint: Hide helper table columns.
  5. Task. Compare your pattern result to an Excel pivot (fictional).
    Hint: Within rounding.
  6. Task. Break a pattern on purpose (wrong relationship) and observe.
    Hint: Then fix.
  7. Task. Write interview lines for three patterns.
    Hint: Why/What/How.
  8. Task. Add a status measure for conditional formatting.
    Hint: IF threshold.
  9. Task. Link one pattern to Ch 47 time intel.
    Hint: One sentence.
  10. Task. Link iterators need to Ch 46.
    Hint: When SUM is not enough.
  11. Task. Card-revise patterns without opening Desktop.
    Hint: Use Ravi Sir cards.
  12. Task. Refactor calc1/calc2 names to business words.
    Hint: Kindness to future-you.

Loading DAX practice tasks...

  1. Sell Through (Medium): Shown per Warehouses Name. Measure: Sell Through = SUM of UnitsOut ÷ SUM of UnitsIn.
  2. Share of UnitsIn (Medium): Shown per Warehouses Name. Measure: UnitsIn Share = SUM of UnitsIn ÷ SUM of UnitsIn for all of Warehouses.
  3. UnitsIn for Electronics only (Medium): Shown per City. Measure: UnitsIn Electronics = SUM of UnitsIn where ItemGroup is "Electronics".
  4. Sell Through against target (Hard): Shown per Warehouses Name. Measure: Sell Through Status = "On target" when Sell Through is >= 0.8, else "Check".
  5. Top 3 only (Hard): Shown per Warehouses Name. Measure: Top 3 UnitsIn = [Units Received] when the rank is 3 or better, else blank.
  6. UnitsIn of the top 2 (Hard): Measure: Top 2 UnitsIn = [Units Received] for the 2 best Warehouses names.
  7. UnitsIn in the first half-year (Medium): Shown per Country. Measure: H1 UnitsIn = SUM of UnitsIn for CountDate before 1 Oct 2025.
  8. Average UnitsIn per row (Medium): Shown per Warehouses Name. Measure: UnitsIn per Row = SUM of UnitsIn ÷ number of rows.
  9. Rows with high stock-out days (Medium): Shown per Country. Measure: High StockoutDays Rows = number of rows where StockoutDays is more than 5.
  10. Sell Through for Electronics (Hard): Shown per Country. Measure: Sell Through Electronics = Sell Through where ItemGroup is "Electronics".

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

Common measure patterns. Most business measures follow a few patterns.Revision card

मराठी cards