Pattern cookbook
Percent of total, ranking, Pareto, new versus returning customers, budget variance, calculation groups.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Build percent of total, ranking, Pareto and new-versus-returning customer measures.
- Write budget variance, semi-additive balance and calculation group measures.
- Add a what-if parameter and a dynamic measure choice to a report.
- Fix wrong totals and subtotals with the right pattern.
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.
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
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
NAMEOFentries. 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 Choicetable (Enter Data) andSelected 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"
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.