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 2 of 24
The real wordsTheory

DAX

DAX (Data Analysis Expressions)The formula language of Power BI, Power Pivot and Analysis Services. It looks like Excel's formula language and behaves differently, because it works on whole columns and tables rather than on cells.

Three things can be written in it: a calculated column, a measure, and a calculated table.

There is no A1 in DAX. A formula refers to Table[Column], never to a cell, so a formula cannot depend on where it happens to sit.

Check against your class slides.

← BackContinue →