Lighthouse

Level 7 · Work it out once with VAR

From Laura Chen, Pricing Analyst

“I wrote a Discount Share measure: how much of the list value of completed orders we gave away in discounts. It's right, but it asks for Gross Sales three times per cell and our contractor flagged it. Can you rewrite it so each measure is worked out once?”

Your goal

The report has Sales (net, completed orders) and Gross Sales (quantity × list unit_price, completed orders). Laura's version is in the editor as a comment. Write Discount Share = (Gross Sales − Sales) ÷ Gross Sales, blank when Gross Sales isn't above zero, storing Gross Sales and Sales in variables so each is evaluated once.

Fill [Discount Share] on every row of the visualnot done yet
The Total row right toonot done yet

Your measure

DAX

Visual: A matrix: rows = products[category]; values = [Gross Sales], [Sales], [Discount Share]
Measures in the report: Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")Gross Sales = CALCULATE(SUMX(order_items, order_items[quantity] * order_items[unit_price]), orders[status] = "completed")

Data pane: tables and columns

order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
returns return_id, order_item_id, returned_on, reason, refund_amount
Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday

Loading the editor…

Reference solution

Visual[Discount Share] fills in when you press Check

The visual before your answer
category	Gross Sales	Sales
Decor	839,177.12	822,667.2
Furniture	1,421,945	1,393,163.85
Kitchen	817,460.81	799,150.28
Lighting	1,021,049.99	1,001,843.51
Outdoor	1,324,874.49	1,297,801.77
Textiles	591,561.12	579,657.83
Total	6,016,068.53	5,894,284.44

Mote checks your result

Press Check. Mote fills in your answer and compares it with what the report needs.

The lesson

Laura’s measure is correct, and it does the same work three times. In a matrix with six categories and a Total, that is 21 evaluations of Gross Sales where 7 would do. On a page with a dozen such measures and millions of rows, it adds up.

What you are working in

This page is a practice report, not Power BI: a matrix of categories with Gross Sales, Sales and your measure. The engine evaluates your measure in every cell and compares each with the expected value; it checks the numbers, so the variables are up to you. In Power BI Desktop: select the measure in the Data pane and edit it in the formula bar; Shift+Enter adds a new line.

VAR and RETURN

Discount Share =
VAR gross = [Gross Sales]
VAR net = [Sales]
RETURN IF(gross > 0, DIVIDE(gross - net, gross))

VAR name = expression evaluates the expression once, where it is defined, in the filter context of the cell, and stores the result.
You can have as many VARs as you like; each can use the ones above it.
RETURN ends the list and gives the measure’s result, built from the variables.

The variable names are yours. They can’t contain spaces or clash with a table name, and short, plain names (gross, net, share) read best.

Faster, and easier to read

Power BI’s engine sometimes notices that the same measure appears twice and reuses the result, but not always, especially once filters or iterators are involved. A variable guarantees it. The bigger win is often readability: each line names one step, so the next person (or you, in six months) can see what the measure does, and you can debug it by returning a single variable: RETURN gross shows what that step gives in every cell.

Variables are fixed once set

A variable is a value, not a formula. Here that is what you want. It surprises people when CALCULATE comes after it:

Web Share =
VAR total = [Sales]
RETURN DIVIDE(CALCULATE(total, orders[channel] = "web"), total)

This returns 100% everywhere: total was already worked out before CALCULATE changed the filter. Put the measure, not the variable, inside CALCULATE: CALCULATE([Sales], orders[channel] = "web").

DIVIDE instead of /

DIVIDE(a, b) returns a blank when b is zero or blank, instead of an error or infinity. Here the IF already guards against it, but DIVIDE is the habit to keep: it also has an optional third argument for what to return instead of blank.

Common mistakes

A variable inside CALCULATE expecting it to change with the new filter.
Forgetting RETURN. A measure with VARs must end with RETURN and an expression.
Spaces in variable names. VAR gross sales doesn’t parse; use gross or gross_sales.

Level 7 · Work it out once with VAR

Your goal

Your measure

Reference solution

Mote checks your result

What Laura gets, and why

The lesson

What you are working in

VAR and RETURN

Faster, and easier to read

Variables are fixed once set

DIVIDE instead of /

Common mistakes

Lantern lit!

Level 7 · Work it out once with VAR

The request

Your goal

Your measure

Reference solution

Mote checks your result

What Laura gets, and why

The lesson

What you are working in

VAR and RETURN

Faster, and easier to read

Variables are fixed once set

DIVIDE instead of /

Common mistakes

Cookie choices