Skip to content
Sem 5 Prep
← Visual AnalyticsDAX: calculated columns, measures and the profit margin ratio

Formulas for this chapter

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
Step 1 of 24
The ideaTheory

Two ways to get a number

You want profit margin. Two routes.

Route one: add a column to every one of 120,000 rows, each holding that row's margin. Route two: write one formula that divides total profit by total sales, whenever anybody asks.

Route one is a calculated column. Route two is a measure. They give different answers, and knowing which is which is the whole chapter.

Check against your class slides. No Power BI material exists in the export, so this chapter is reconstructed from the outline's topic wording and general knowledge.

← BackContinue →