Power BI Intermediate Lesson 5: Variables, DIVIDE and Readable DAX

📎 This article includes 3 downloadable practice files ↓

📘 Power BI Intermediate Course · Lesson 5 of 10

List Value  = SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )

Discount Rs =
VAR ListValue = [List Value]
VAR Sold      = SUM ( Sales[Amount] )
RETURN ListValue - Sold

Discount %  = DIVIDE ( [Discount Rs], [List Value] )

Practice model: 700 orders, 275 sold below list price, ₹8,42,138 discount in total (2.91%).

Why variables

• Readable: each step has a name.
• Faster: a variable is calculated once, even if used three times.
• Debuggable: temporarily RETURN ListValue to see an intermediate value in a card.
• Correct context: a variable is evaluated where it’s defined, which is how “growth vs last year” keeps the current value fixed.

DIVIDE, not /

DIVIDE(a, b) returns blank when b is 0 or blank, so a region with no sales last year shows an empty cell instead of an error or infinity. Add a third argument for a different fallback: DIVIDE(a, b, 0).

Practice

Import pbi-intermediate-model.xlsx into Power BI Desktop (free), create the relationships listed at the top of the .dax file, mark the Date table as a date table and add this lesson’s measures. Compare your card and table values with the lesson’s sheet in the expected-results file. Those numbers were worked out independently in Python from the same data, so a mismatch means a filter or relationship to check.