Profit margin ratio
Profit Margin Ratio = DIVIDE ( [Total Profit], [Total Sales], 0 )
The measure the outline names. Always built from the two total measures rather than from a per-row column, so it is recomputed correctly at every level of every visual. Format as a percentage.
- [Total Profit]
- SUM(Sales[Profit]) under the current filters
- [Total Sales]
- SUM(Sales[Sales]) under the current filters
- 0
- The alternate DIVIDE returns when sales are zero or blank
Total cost from sales and profit
Total Cost = [Total Sales] - [Total Profit] cost ratio + margin = 100%
Whenever a question gives two of sales, profit and cost and asks for the third. The identity that margin plus cost ratio makes 100 per cent is a free check on the arithmetic.
- [Total Sales]
- Revenue under the current filters
- [Total Profit]
- Profit under the current filters
- Total Cost
- What the sales cost to make and deliver
Iterate, then aggregate (SUMX)
SUMX ( Table, expr ) evaluates expr per row and adds; SUM(a) * SUM(b) is NOT SUMX(T, a*b)
Whenever the number to be added is computed per row from two or more columns: quantity times price, weight times rate. Using SUM on each column and multiplying introduces every cross pairing and is usually wrong by a large factor.
- Table
- The table to iterate, normally the fact table
- expr
- The per-row expression, evaluated in that row's row context
- AVERAGEX
- The same iteration, averaged instead of summed
Weighted average of ratios
overall ratio = SUM(numerators) / SUM(denominators) NOT the plain average of the ratios
Any time a question gives several percentages and asks for the overall figure. Recover each numerator first, add both columns, then divide. A plain average is only correct when every denominator is equal.
- numerator
- The additive top of the ratio, e.g. profit
- denominator
- The additive bottom, e.g. sales, which supplies the weight
- weight
- Each item's denominator as a share of the total denominator
Year-over-year growth
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
Needs a continuous Date table marked as the date table, and all date slicing done through it. Use DIVIDE, because a product launched this year has a prior-year base of zero and growth from zero is undefined.
- 'Date'[Date]
- The date column of a gap-free date dimension
- SAMEPERIODLASTYEAR
- Shifts the current filter context back one year
- YoY
- Growth as a fraction; format as a percentage