Why DAX powers calculations in Power BI - Business Case Study

Scenario Mode

👤 Your Role: You are a business analyst working with sales data in Power BI.

📋 Request: Your manager wants you to create dynamic calculations to analyze sales performance by product and region.

📊 Data: You have a sales table with columns: Date, Region, Product, Sales Amount, and Quantity Sold.

🎯 Deliverable: Create calculated measures using DAX to show total sales, average sales per product, and sales growth compared to the previous period. Then build a report with visuals showing these insights.

Sample Data

Date	Region	Product	Sales Amount	Quantity Sold
2024-01-01	North	Widget A	1000	10
2024-01-01	South	Widget B	1500	15
2024-02-01	North	Widget A	1200	12
2024-02-01	South	Widget B	1600	16
2024-03-01	North	Widget A	1100	11
2024-03-01	South	Widget B	1700	17
2024-01-01	East	Widget C	900	9
2024-02-01	East	Widget C	950	10
2024-03-01	East	Widget C	1000	11

1

Step 1: Create a calculated measure for Total Sales using DAX.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

Total Sales sums all sales amounts dynamically based on filters.

2

Step 2: Create a calculated measure for Average Sales per Product.

Average Sales per Product = DIVIDE([Total Sales], DISTINCTCOUNT('Sales'[Product]), 0)

Expected Result

Calculates average sales amount per product considering current filters.

3

Step 3: Create a calculated measure for Sales Growth compared to previous month.

Sales Growth % = DIVIDE([Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH('Sales'[Date])), CALCULATE([Total Sales], PREVIOUSMONTH('Sales'[Date])), 0) * 100

Expected Result

Shows percentage growth or decline in sales compared to the previous month.

4

Step 4: Build a report with a line chart showing Total Sales over time by Region.

Configure line chart: Axis = Date, Legend = Region, Values = Total Sales measure

Expected Result

Line chart displays sales trends over months for each region.

5

Step 5: Add a table visual showing Product, Total Sales, Average Sales per Product, and Sales Growth %

Table columns: Product, Total Sales, Average Sales per Product, Sales Growth %

Expected Result

Table shows sales metrics per product with dynamic calculations.

Final Result

---------------------------------------------
| Date       | North | South | East  | Total |
|------------|-------|-------|-------|-------|
| 2024-01-01 | 1000  | 1500  | 900   | 3400  |
| 2024-02-01 | 1200  | 1600  | 950   | 3750  |
| 2024-03-01 | 1100  | 1700  | 1000  | 3800  |
---------------------------------------------

Product Summary Table:
-------------------------------------------------
| Product  | Total Sales | Avg Sales/Product | Growth % |
|----------|-------------|-------------------|----------|
| Widget A | 3300        | 1100              | 5.0%     |
| Widget B | 4800        | 1600              | 6.25%    |
| Widget C | 2850        | 950               | 5.26%    |
-------------------------------------------------

✓Sales increased steadily from January to March across all regions.

✓Widget B has the highest total sales and average sales per product.

✓Sales growth percentage shows positive trends month over month.

Bonus Challenge

Create a DAX measure to calculate the cumulative total sales year-to-date.

Show Hint

Use the TOTALYTD function with the Date column and Total Sales measure.

Practice

1. What is the main purpose of DAX in Power BI?

easy

A. To perform calculations and create measures

B. To design the layout of reports

C. To import data from external sources

D. To create visual themes and colors

Solution

1. Step 1: Understand DAX role in Power BI

   DAX is a formula language used to create calculations like sums and averages.

2. Step 2: Differentiate from other tasks

   Designing layouts, importing data, and theming are done by other Power BI features, not DAX.

3. Final Answer:

   To perform calculations and create measures -> Option A

4. Quick Check:

   DAX = calculations [OK]

Hint: DAX = calculations, not design or import [OK]

Common Mistakes:

• Confusing DAX with report design tools
• Thinking DAX imports data
• Assuming DAX controls colors or themes

2. Which of the following is the correct syntax to create a simple sum measure in DAX?

easy

A. Total Sales = SUM(Sales[Amount])

B. Total Sales := SUM(Sales[Amount])

C. Total Sales = SUMM(Sales[Amount])

D. Total Sales = SUM[Sales.Amount]

Solution

1. Step 1: Review correct DAX sum syntax

   The SUM function uses parentheses and column reference with table[column] format.

2. Step 2: Identify syntax errors in other options

   Total Sales := SUM(Sales[Amount]) uses := which is incorrect in Power BI measures; Total Sales = SUMM(Sales[Amount]) misspells SUM; Total Sales = SUM[Sales.Amount] uses wrong column reference syntax.

3. Final Answer:

   Total Sales = SUM(Sales[Amount]) -> Option A

4. Quick Check:

   SUM function syntax = Total Sales = SUM(Sales[Amount]) [OK]

Hint: SUM uses parentheses and table[column] format [OK]

Common Mistakes:

• Using := instead of = for measures
• Misspelling function names like SUMM
• Incorrect column reference syntax

3. Given the DAX measure:
Average Sales = AVERAGE(Sales[Amount])
What will this measure return when used in a report?

medium

A. The total sum of all sales amounts

B. The average value of the sales amounts

C. The count of sales transactions

D. The maximum sales amount

Solution

1. Step 1: Understand the AVERAGE function

   AVERAGE calculates the mean value of the specified column's numbers.

2. Step 2: Compare with other options

   SUM gives total, COUNT counts rows, MAX finds highest value, so they don't match the measure.

3. Final Answer:

   The average value of the sales amounts -> Option B

4. Quick Check:

   AVERAGE function returns mean = The average value of the sales amounts [OK]

Hint: AVERAGE means mean value, not sum or count [OK]

Common Mistakes:

• Confusing AVERAGE with SUM or COUNT
• Assuming it returns max value
• Ignoring function purpose

4. Identify the error in this DAX measure:
Profit = SUM(Sales[Amount]) - SUM(Costs[Cost)

medium

A. Minus sign (-) is not allowed in DAX

B. SUM function cannot be used with Costs table

C. Measure name cannot be 'Profit'

D. Missing closing bracket in Costs column reference

Solution

1. Step 1: Check syntax of column references

   The Costs column reference is missing a closing bracket ")" after Costs[Cost].

2. Step 2: Validate other parts

   SUM can be used on any numeric column; measure names can be any valid identifier; minus sign is valid for subtraction.

3. Final Answer:

   Missing closing bracket in Costs column reference -> Option D

4. Quick Check:

   Bracket mismatch causes syntax error = Missing closing bracket in Costs column reference [OK]

Hint: Check all brackets carefully in column references [OK]

Common Mistakes:

• Ignoring missing or extra brackets
• Thinking function usage is wrong
• Assuming measure names are restricted
• Misunderstanding operators allowed

5. You want to create a measure that calculates the total sales only for the current year dynamically. Which DAX expression correctly achieves this?

hard

A. Total Sales CY = SUM(Sales[Amount]) WHERE YEAR(Sales[Date]) = YEAR(TODAY())

B. Total Sales CY = CALCULATE(SUM(Sales[Amount]), YEAR(Sales[Date]) = YEAR(TODAY()))

C. Total Sales CY = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY())))

D. Total Sales CY = SUMX(FILTER(Sales, Sales[Date] = TODAY()), Sales[Amount])

Solution

1. Step 1: Understand CALCULATE with FILTER usage

   CALCULATE modifies filter context; FILTER creates a table filtered by year matching current year.

2. Step 2: Analyze each option's correctness

   Total Sales CY = CALCULATE(SUM(Sales[Amount]), YEAR(Sales[Date]) = YEAR(TODAY())) uses a boolean expression directly inside CALCULATE which is invalid; Total Sales CY = SUM(Sales[Amount]) WHERE YEAR(Sales[Date]) = YEAR(TODAY()) uses SQL-like WHERE which is not DAX syntax; Total Sales CY = SUMX(FILTER(Sales, Sales[Date] = TODAY()), Sales[Amount]) filters only today's date, not the whole year.

3. Final Answer:

   Total Sales CY = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY()))) -> Option C

4. Quick Check:

   Use CALCULATE with FILTER for dynamic year filter [OK]

Hint: Use CALCULATE with FILTER for dynamic date filters [OK]

Common Mistakes:

• Using boolean conditions directly in CALCULATE
• Applying SQL syntax like WHERE in DAX
• Filtering only one day instead of full year