<!-- https://learnsql.cpetoday.com/analyze/patterns -->
# DAX pattern cookbook

Recipes use a sales star schema: Sales (SalesAmount, TotalCost, SalesQuantity, OrderDate/DateKey), Calendar, Product, Category, Subcategory, Store, Channel, Geography, Customer. Adapt the names.

## Percent of total and share of parent
```
% of Grand Total = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS()))
% of Selected = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED()))
% of Column Total (Channel on columns) = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Product)))
% of Parent = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Subcategory)))
% of Group (any hierarchy level) =
VAR Level = IF(ISINSCOPE(Product[Product]), 1, IF(ISINSCOPE(Subcategory[Subcategory]), 2, 3))
RETURN SWITCH(Level,
    1, DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Product[Product]))),
    2, DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Subcategory[Subcategory]))),
    DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED())))
```
ALLSELECTED() with no arguments returns all selected rows in the whole model. Keep the denominator's filter removal at the same level as the numerator's grouping.

## Year to date, prior year, growth
```
Sales YTD = TOTALYTD([Total Sales], 'Calendar'[Date])
Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])
YTD vs PY YTD % = VAR cur = [Sales YTD] VAR py = CALCULATE([Sales YTD], SAMEPERIODLASTYEAR('Calendar'[Date])) RETURN DIVIDE(cur - py, py)
```
See the time intelligence topic for fiscal years, rolling 12 months, and partial years.

## Rolling average and moving window
```
Sales Rolling 12M = CALCULATE([Total Sales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))
Avg Monthly Sales Last 12M =
DIVIDE(CALCULATE([Total Sales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH)), 12)
Sales Last 30 Days = CALCULATE([Total Sales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -30, DAY))
```

## Ranking and Top N with an "Others" row
```
Product Rank = IF(HASONEVALUE(Product[Product]), RANKX(ALLSELECTED(Product[Product]), [Total Sales], , DESC, Dense))
Top N Sales = VAR N = SELECTEDVALUE('Top N'[Top N Value], 10)
              RETURN CALCULATE([Total Sales], TOPN(N, ALLSELECTED(Product[Product]), [Total Sales], DESC))
Is Top N = IF([Product Rank] <= SELECTEDVALUE('Top N'[Top N Value], 10), 1, 0)
```
Create `Top N = GENERATESERIES(3, 20, 1)` as a disconnected What-if table. Filter a visual by the Is Top N measure (= 1). For an "Others" bar build a small table `{"Top N", "Others"}` and SWITCH on it.

## Pareto / ABC classification (cumulative percent of sales)
```
Product Sales Rank = RANKX(ALL(Product[Product]), [Total Sales], , DESC, Dense)
Cumulative Sales % =
VAR ThisRank = [Product Sales Rank]
VAR Running = SUMX(FILTER(ALL(Product[Product]), [Product Sales Rank] <= ThisRank), [Total Sales])
RETURN DIVIDE(Running, CALCULATE([Total Sales], ALL(Product[Product])))
ABC Class = SWITCH(TRUE(), [Cumulative Sales %] <= 0.8, "A", [Cumulative Sales %] <= 0.95, "B", "C")
```
Use ALL on the product column in both the rank and the denominator so the class does not change with slicers (use ALLSELECTED to make it respond). Iterating a unique product list keeps it fast enough for a few thousand products.

## Distinct counts, new versus returning customers
```
Customers = DISTINCTCOUNT(Sales[CustomerKey])
First Purchase Date = CALCULATE(MIN(Sales[OrderDate]), ALL('Calendar'))   -- per customer in a customer visual
New Customers =
COUNTROWS(FILTER(VALUES(Sales[CustomerKey]),
    CALCULATE(MIN(Sales[OrderDate]), ALL('Calendar')) >= MIN('Calendar'[Date]) &&
    CALCULATE(MIN(Sales[OrderDate]), ALL('Calendar')) <= MAX('Calendar'[Date])))
Returning Customers = [Customers] - [New Customers]
Active Customers Last 90 Days =
CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -90, DAY))
```
ALL('Calendar') removes the date filter so MIN is the customer's first ever purchase.

> **Tip.** For performance with millions of customers, precompute First Purchase Date as a calculated column on Customer.

## Customers who bought A but not B, basket analysis
```
Customers Who Bought Computers = CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]), Category[Category] = "Computers")
Bought Computers and Cameras =
COUNTROWS(INTERSECT(
    CALCULATETABLE(VALUES(Sales[CustomerKey]), Category[Category] = "Computers"),
    CALCULATETABLE(VALUES(Sales[CustomerKey]), Category[Category] = "Cameras and camcorders")))
Bought Computers Not Cameras =
COUNTROWS(EXCEPT(
    CALCULATETABLE(VALUES(Sales[CustomerKey]), Category[Category] = "Computers"),
    CALCULATETABLE(VALUES(Sales[CustomerKey]), Category[Category] = "Cameras and camcorders")))
```
INTERSECT, EXCEPT and UNION compare whole tables, which is the clean way to express "both", "but not" and "either".

## Budget versus actual and variance
Put budget in its own fact at a coarser grain (month, department) related to the same Calendar and a shared dimension; do not force it into the sales table. Measures:
```
Actual = [Total Sales]
Budget = SUM(Budget[Amount])
Variance = [Actual] - [Budget]
Variance % = DIVIDE([Variance], [Budget])
Budget at Month Grain Only = IF(ISINSCOPE('Calendar'[Date]), BLANK(), [Budget])   -- hide a monthly budget at day level
```
When budget is monthly and actual is daily, hide budget at the day level, or allocate it with a day count: `DIVIDE([Budget], DAY(EOMONTH(MAX('Calendar'[Date]), 0)))`.

## Semi-additive balances (inventory, bank balance, headcount)
```
Closing Balance = CALCULATE(SUM(Balances[Amount]), LASTDATE('Calendar'[Date]))
Closing Balance (last date with data) =
CALCULATE(SUM(Balances[Amount]), LASTNONBLANK('Calendar'[Date], CALCULATE(COUNTROWS(Balances))))
Average Balance = AVERAGEX(VALUES('Calendar'[Date]), CALCULATE(SUM(Balances[Amount])))
```

## Fix wrong totals and subtotals
> **Watch out.** A per-row calculation shows right in the rows but a wrong total? The total row evaluates the measure once on the whole set.

Iterate the row level:
```
Commission = SUMX(VALUES(Employee[Employee]), IF([Total Sales] > 50000, [Total Sales] * 0.05, [Total Sales] * 0.03))
```
To show a value only at the lowest level or hide subtotals: `IF(ISINSCOPE(Product[Product]), [Total Sales])`. To make a matrix show rows with no sales, add a measure like `[Total Sales] + 0` or use "Show items with no data" on the field.

## Field parameters and dynamic measure choice
- Field parameters (Modeling > New parameter > Fields): add Total Sales, Total Cost and Gross Margin; Desktop creates a calculated table of `NAMEOF` entries. Put the slicer on it, and put the parameter column in a visual's value well (or axis) and the visual switches measures (or columns) with the selection. Field parameters need Power BI Desktop; Excel Power Pivot does not have them.
- The older, portable way (works in Power Pivot too): a disconnected `Measure Choice` table (Enter Data) and `Selected Measure = SWITCH(SELECTEDVALUE('Measure Choice'[Measure]), "Sales", [Total Sales], "Cost", [Total Cost], [Total Sales])`.
- Dynamic axis: a field parameter on columns (Category, Subcategory, Brand), or a calculated column per option plus SWITCH in a measure that chooses what to show.

## Calculation groups for time intelligence (YTD, prior year, YoY %)
A calculation group applies one formula to every measure through `SELECTEDMEASURE()`, so you define YTD, PY and YoY % once instead of once per measure. Create it in Power BI Desktop (Model view > Calculation group; Desktop turns on "discourage implicit measures"), or with Tabular Editor. Items:
```
Current = SELECTEDMEASURE()
YTD  =  CALCULATE(SELECTEDMEASURE(), DATESYTD('Calendar'[Date]))
PY   =  CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY  =  SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY % =
VAR PY = CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Calendar'[Date]))
RETURN DIVIDE(SELECTEDMEASURE() - PY, PY)       -- Format string expression: "0.0%"
```
Put the calculation group column on a matrix's columns or in a slicer. Notes: calculation items do not change the data type, so give a percent item a format string expression (`"0.0%"`); precedence decides which group wins when two apply; measures defined with a calculation item inside CALCULATE interact, so test totals; in Power Pivot for Excel, calculation groups cannot be created in the UI, so use separate measures instead (Sales YTD, Sales PY, YoY %).

## Conditional formatting and dynamic labels
```
Margin Color = IF([Gross Margin %] >= 0.55, "#1B7F3B", IF([Gross Margin %] >= 0.45, "#B7791F", "#C0392B"))   -- Format > Conditional formatting > Field value
Sales Trend Arrow = IF([YoY %] >= 0, "▲", "▼")
Dynamic Title = "Sales for " & SELECTEDVALUE('Calendar'[Year], "all years") & " in " & SELECTEDVALUE(Channel[Channel], "all channels")
Top Product Label = "Best seller: " & CALCULATE(FIRSTNONBLANK(Product[Product], 1), TOPN(1, ALLSELECTED(Product[Product]), [Total Sales]))
```
Bind a color measure through Conditional formatting > Format style: Field value. Bind a dynamic title with the fx button next to Title. A measure that returns text can feed a card.

## Percentiles, median, histograms, outliers
```
Median Sale = MEDIANX(Sales, Sales[SalesAmount])
P90 Sale = PERCENTILEX.INC(Sales, Sales[SalesAmount], 0.9)
StdDev = STDEVX.P(Sales, Sales[SalesAmount])
Sales Bin = SELECTEDVALUE(Bins[Bin])      -- Bins = GENERATESERIES(0, 5000, 500)
Orders in Bin = VAR lo = SELECTEDVALUE(Bins[Bin]) RETURN CALCULATE(COUNTROWS(Sales), Sales[SalesAmount] >= lo, Sales[SalesAmount] < lo + 500)
```

## Cohorts and retention
Add `Customer[First Order Month]` (calculated column, MIN of order date in the customer's sales, truncated to month) and a Cohort Age measure: `DATEDIFF(MIN(Customer[First Order Month]), MAX('Calendar'[Date]), MONTH)`. Show a matrix with first order month on rows, cohort age on columns, and `DISTINCTCOUNT(Sales[CustomerKey])` as values, divided by the cohort size for a retention percentage.

## Price, volume and mix variance (outline)
Compute Sales PY, Units PY and Price = Sales / Units for each period. Volume effect = (Units - Units PY) * Price PY. Price effect = (Price - Price PY) * Units. Mix needs a product-level iteration: SUMX over Product of the same formulas then subtract. Compute the effects at product level and sum, so they reconcile to the total change.

## Calendar table in Power Query (M)
```
let
    Start = #date(2016, 1, 1), End = #date(2018, 12, 31),
    Dates = List.Dates(Start, Duration.Days(End - Start) + 1, #duration(1, 0, 0, 0)),
    T = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    Typed = Table.TransformColumnTypes(T, {{"Date", type date}}),
    Year = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
    Month = Table.AddColumn(Year, "Month", each Date.ToText([Date], "MMMM", "en-US"), type text),
    MonthNo = Table.AddColumn(Month, "Month Number", each Date.Month([Date]), Int64.Type)
in MonthNo
```

## What-if parameter
Modeling > New parameter > Numeric range creates a table (GENERATESERIES) and a measure `Parameter Value = SELECTEDVALUE('Discount'[Discount], 0)`. Use it inside measures: `Sales After Discount = [Total Sales] * (1 - [Discount Value])`.

## Show the selected slicer value, last refresh, and counts of selections
```
Selected Years = CONCATENATEX(VALUES('Calendar'[Year]), 'Calendar'[Year], ", ", 'Calendar'[Year], ASC)
Last Refresh = "Updated " & FORMAT(MAX(RefreshLog[Timestamp]), "mmm d, yyyy h:mm AM/PM")   -- RefreshLog table: a Power Query step with DateTime.LocalNow()
Selected Count = COUNTROWS(VALUES(Product[Brand])) & " of " & COUNTROWS(ALL(Product[Brand])) & " brands"
```
