Variables (VAR/RETURN) in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to create a measure that calculates the average sales per customer for each product category, using variables to make the calculation clear and efficient.

📊 Data: You have a sales table with columns: ProductCategory, CustomerID, and SalesAmount.

🎯 Deliverable: Create a DAX measure using VAR and RETURN to calculate average sales per customer by product category, and build a bar chart showing this measure by product category.

Sample Data

ProductCategory	CustomerID	SalesAmount
Electronics	C001	200
Electronics	C002	150
Electronics	C001	100
Clothing	C003	80
Clothing	C004	120
Clothing	C003	40
Groceries	C005	50
Groceries	C006	70
Groceries	C005	30

1

Step 1: Create a new measure in Power BI named 'Total Sales' that sums SalesAmount.

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Total Sales sums all sales amounts correctly.

2

Step 2: Create a new measure named 'Distinct Customers' that counts unique CustomerID per ProductCategory.

Distinct Customers = DISTINCTCOUNT(Sales[CustomerID])

Expected Result

Distinct Customers counts unique customers per category.

3

Step 3: Create a new measure named 'Average Sales per Customer' using VAR and RETURN to calculate average sales per customer by product category.

Average Sales per Customer = VAR TotalSales = SUM(Sales[SalesAmount]) VAR CustomerCount = DISTINCTCOUNT(Sales[CustomerID]) RETURN DIVIDE(TotalSales, CustomerCount, 0)

Expected Result

Average Sales per Customer calculates average sales per customer for each product category.

4

Step 4: Build a bar chart visual in Power BI with ProductCategory on the axis and 'Average Sales per Customer' as the value.

Axis: Sales[ProductCategory] Values: Average Sales per Customer

Expected Result

Bar chart shows average sales per customer for Electronics, Clothing, and Groceries.

Final Result

ProductCategory       Average Sales per Customer
------------------------------------------------
Electronics           ██████████████ 150
Clothing              █████████ 80
Groceries             ██████ 50

✓Electronics has the highest average sales per customer at 150.

✓Clothing has a moderate average sales per customer at 80.

✓Groceries has the lowest average sales per customer at 50.

Bonus Challenge

Modify the measure to handle cases where there are no customers to avoid division by zero errors and display a friendly message in the report.

Show Hint

Use the DIVIDE function with the third parameter for alternate result and consider adding an IF statement to check for zero customers.