Gross margin with variables
Advanced DAX · Intermediate · Chapter 9 of 10. Variables, ranking, iterators and dynamic labels.
The request, from Owen Price, Founder, Copperbean Coffee: "Revenue is fine, but what do we actually keep from each drink after the cost of the cup?" A measure named Margin %.
New dataset! Copperbean Coffee has five shops: Orders has cups and revenue per shop, drink, month and hour; Drink has each drink’s price and Cost per Cup. Variables (VAR … RETURN) name the steps of a calculation, so long formulas stay readable and each part is computed once.
Task: Create Margin % = VAR Cost = SUMX(Orders, Orders[Cups] RELATED(Drink[Cost per Cup])) RETURN DIVIDE([Total Revenue] - Cost, [Total Revenue]).
Practice data: Copperbean Coffee, a fictional chain of coffee shops. The model has the Orders, Drink and Store tables.
DAX functions used: SUMX, RELATED, DIVIDE.
How it works: the exercise opens a practice copy of the Power BI report editor in your browser. Do the task, press Check, and your result is compared with the expected answer in seconds. Hints are there if you get stuck.