Why intermediate DAX solves business questions in Power BI - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants to understand monthly sales performance, compare it to last year, and identify top products driving growth.

📊 Data: You have sales data with columns: Date, Product, Region, Sales Amount, and Quantity Sold for the last two years.

🎯 Deliverable: Create a Power BI report with measures showing monthly sales, year-over-year growth, and top 3 products by sales.

Sample Data

Date	Product	Region	Sales Amount	Quantity Sold
2023-01-15	Widget A	North	1200	10
2023-01-20	Widget B	South	800	8
2023-02-10	Widget A	North	1500	12
2023-02-15	Widget C	East	700	7
2022-01-18	Widget A	North	1000	9
2022-01-25	Widget B	South	900	9
2022-02-12	Widget A	North	1300	11
2022-02-20	Widget C	East	600	6

1

Step 1: Create a Date table to enable time intelligence calculations.

Use Power BI's built-in Date table or create with DAX: Date = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))

Expected Result

A Date table covering all sales dates from 2022-01-18 to 2023-02-20.

2

Step 2: Create a measure to calculate total sales amount.

Total Sales = SUM(Sales[Sales Amount])

Expected Result

Total Sales measure sums all sales amounts correctly.

3

Step 3: Create a measure to calculate sales for the previous year for comparison.

Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Expected Result

Sales LY measure shows sales amount for the same period last year.

4

Step 4: Create a measure to calculate year-over-year sales growth percentage.

YoY Growth % = DIVIDE([Total Sales] - [Sales LY], [Sales LY], 0)

Expected Result

YoY Growth % measure shows the percentage change in sales compared to last year.

5

Step 5: Create a measure to rank products by total sales.

Product Rank = RANKX(ALL(Sales[Product]), [Total Sales], , DESC, DENSE)

Expected Result

Product Rank measure assigns ranks to products based on sales, with 1 being highest.

6

Step 6: Create a visual table showing Product, Total Sales, and Product Rank, filtered to top 3 products.

Use a table visual with filters: Product Rank <= 3

Expected Result

Table shows top 3 products by sales with their sales amounts and ranks.

7

Step 7: Create a line chart showing monthly Total Sales and Sales LY over time.

Axis: 'Date'[Month], Values: Total Sales and Sales LY measures

Expected Result

Line chart displays monthly sales trends and last year's sales for comparison.

Final Result

Monthly Sales Trend

Month | Total Sales | Sales LY | YoY Growth %
---------------------------------------------
Jan   | 2000        | 1900     | 5.26%
Feb   | 2200        | 1900     | 15.79%

Top 3 Products by Sales
-----------------------
Rank | Product  | Total Sales
-----------------------------
1    | Widget A | 5000
2    | Widget B | 1700
3    | Widget C | 1300

✓Sales increased by about 5% in January and nearly 16% in February compared to last year.

✓Widget A is the top-selling product driving most of the growth.

✓Widget B and Widget C follow as second and third top products.

Bonus Challenge

Create a measure to calculate the percentage contribution of each product to total sales dynamically.

Show Hint

Use DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Product]))) to get product sales divided by total sales.

Practice

1. What is the main reason intermediate DAX helps solve business questions better than basic DAX?

easy

A. It only works with small datasets.

B. It changes the calculation context to focus on specific data.

C. It uses simpler functions that are easier to understand.

D. It avoids using any filters or conditions.

Solution

1. Step 1: Understand calculation context in DAX

   Intermediate DAX uses functions like CALCULATE to change the context, focusing calculations on specific data subsets.

2. Step 2: Compare with basic DAX

   Basic DAX performs calculations in the default context without filtering or modifying data focus.

3. Final Answer:

   It changes the calculation context to focus on specific data. -> Option B

4. Quick Check:

   Calculation context = focus on data [OK]

Hint: Remember: CALCULATE changes context to answer specific questions [OK]

Common Mistakes:

• Thinking intermediate DAX is simpler than basic DAX
• Believing it only works on small data
• Ignoring the role of filters in calculations

2. Which of the following is the correct syntax to calculate total sales filtered by a specific product category using CALCULATE in DAX?

easy

A. SUM(Sales[Amount]) FILTER Sales[Category] = "Bikes"

B. CALCULATE(SUM(Sales[Amount]), Sales[Category] == "Bikes")

C. CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Bikes"))

D. CALCULATE(Sales[Amount], Sales[Category] == "Bikes")

Solution

1. Step 1: Understand CALCULATE with FILTER

   CALCULATE requires an expression and a filter table; FILTER creates a table filtered by condition.

2. Step 2: Check syntax correctness

   CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Bikes")) correctly uses CALCULATE with SUM and FILTER function with proper syntax.

3. Final Answer:

   CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Bikes")) -> Option C

4. Quick Check:

   CALCULATE + FILTER = correct syntax [OK]

Hint: Use FILTER inside CALCULATE for complex conditions [OK]

Common Mistakes:

• Using '==' instead of '=' in DAX
• Omitting FILTER function when needed
• Wrong order of arguments in CALCULATE

3. Given the measure:
Total Sales Bikes = CALCULATE(SUM(Sales[Amount]), Sales[Category] = "Bikes")
What will be the result if the Sales table has:
- Bikes sales: 1000
- Accessories sales: 500
- Clothing sales: 300
?

medium

A. 1000

B. 0

C. 500

D. 1800

Solution

1. Step 1: Understand the filter applied

   The measure filters Sales to only include rows where Category = "Bikes".

2. Step 2: Sum the filtered sales

   Only Bikes sales (1000) are summed; other categories are excluded.

3. Final Answer:

   1000 -> Option A

4. Quick Check:

   Sum filtered by Bikes = 1000 [OK]

Hint: Filter limits sum to Bikes only [OK]

Common Mistakes:

• Summing all categories ignoring filter
• Confusing Accessories or Clothing sales as Bikes
• Assuming filter includes multiple categories

4. You wrote this DAX measure:
Sales Bikes = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] == "Bikes"))
But it returns an error. What is the problem?

medium

A. Using '==' instead of '=' for comparison in DAX.

B. SUM cannot be used inside CALCULATE.

C. FILTER cannot be used inside CALCULATE.

D. Missing quotation marks around Bikes.

Solution

1. Step 1: Check comparison operator in DAX

   DAX uses single '=' for equality, not '==' like some programming languages.

2. Step 2: Identify syntax error

   Using '==' causes syntax error; replacing with '=' fixes it.

3. Final Answer:

   Using '==' instead of '=' for comparison in DAX. -> Option A

4. Quick Check:

   Use '=' for equality in DAX [OK]

Hint: Use single '=' for comparisons in DAX [OK]

Common Mistakes:

• Confusing DAX syntax with other languages
• Thinking FILTER can't be inside CALCULATE
• Forgetting quotes around text values

5. You want to calculate the percentage growth of sales for the current year compared to the previous year using intermediate DAX. Which approach correctly uses CALCULATE and FILTER to achieve this?

hard

A. Growth % = SUM(Sales[Amount]) - SUM(Sales[Amount])

B. Growth % = SUM(Sales[Amount]) / SUM(Sales[Amount])

C. Growth % = CALCULATE(SUM(Sales[Amount]), Sales[Year] = MAX(Sales[Year]) + 1)

D. Growth % = DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1)), CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1)))

Solution

1. Step 1: Calculate current and previous year sales

   Use SUM(Sales[Amount]) for current year and CALCULATE with FILTER for previous year sales.

2. Step 2: Compute percentage growth

   Subtract previous year sales from current year sales, then divide by previous year sales using DIVIDE for safe division.

3. Final Answer:

   Growth % = DIVIDE(SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1)), CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = MAX(Sales[Year]) - 1))) -> Option D

4. Quick Check:

   Use CALCULATE + FILTER for previous year, DIVIDE for growth [OK]

Hint: Use CALCULATE with FILTER for previous year sales [OK]

Common Mistakes:

• Not filtering previous year correctly
• Dividing by zero without DIVIDE function
• Using wrong year comparison operators