Calculated tables in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a summary table that shows total sales and total quantity sold by each product category.

📊 Data: You have a sales transactions table with columns: OrderID, ProductID, Category, Quantity, and SalesAmount.

🎯 Deliverable: Create a calculated table that groups data by Category and shows total sales and total quantity for each category.

Sample Data

OrderID	ProductID	Category	Quantity	SalesAmount
1001	P01	Electronics	2	400
1002	P02	Clothing	5	150
1003	P03	Electronics	1	200
1004	P04	Home	3	300
1005	P05	Clothing	2	60
1006	P06	Home	4	400
1007	P07	Electronics	3	600
1008	P08	Clothing	1	30

1

Step 1: Open Power BI Desktop and load the sales transactions data into a table named 'Sales'.

Load data from source with columns: OrderID, ProductID, Category, Quantity, SalesAmount.

Expected Result

Sales table with 8 rows loaded.

2

Step 2: Create a new calculated table to summarize total sales and quantity by Category.

CategorySummary = SUMMARIZE(Sales, Sales[Category], "Total Quantity", SUM(Sales[Quantity]), "Total Sales", SUM(Sales[SalesAmount]))

Expected Result

A new table 'CategorySummary' with 3 rows, one for each category: Electronics, Clothing, Home.

3

Step 3: Verify the calculated table values by checking totals for each category.

Check sums: Electronics Quantity=6, Sales=1200; Clothing Quantity=8, Sales=240; Home Quantity=7, Sales=700.

Expected Result

CategorySummary table shows correct totals matching the sales data.

4

Step 4: Create a simple table visual in Power BI report using the 'CategorySummary' table.

Add Table visual with fields: Category, Total Quantity, Total Sales from CategorySummary.

Expected Result

Table visual displays categories with their total quantity and sales.

Final Result

CategorySummary Table
+-------------+---------------+------------+
| Category    | Total Quantity| Total Sales|
+-------------+---------------+------------+
| Electronics | 6             | 1200       |
| Clothing    | 8             | 240        |
| Home        | 7             | 700        |
+-------------+---------------+------------+

✓Electronics category has the highest total sales of 1200.

✓Clothing category sold the highest quantity of 8 units.

✓Home category sales and quantity are moderate compared to others.

Bonus Challenge

Create a calculated table that shows the average sales amount per order for each category.

Show Hint

Use AVERAGEX function on grouped data or divide total sales by count of orders per category.

Practice

1. What is the main purpose of a calculated table in Power BI?

easy

A. To create a new table based on a DAX formula without altering original data

B. To directly edit the source data in the database

C. To visualize data using charts and graphs

D. To export data to Excel automatically

Solution

1. Step 1: Understand calculated tables

   Calculated tables are created using DAX formulas inside Power BI to generate new tables.

2. Step 2: Compare with other options

   They do not edit source data, create visuals, or export data directly.

3. Final Answer:

   To create a new table based on a DAX formula without altering original data -> Option A

4. Quick Check:

   Calculated tables = new tables from DAX [OK]

Hint: Calculated tables create new data, not change original [OK]

Common Mistakes:

• Confusing calculated tables with data editing
• Thinking calculated tables create visuals
• Assuming calculated tables export data

2. Which of the following is the correct syntax to create a calculated table named TopProducts showing top 5 products by sales?

easy

A. TopProducts = TOPN(5, Products, Products[Sales], DESC)

B. TopProducts = FILTER(Products, Products[Sales] > 5)

C. TopProducts = SUMMARIZE(Products, Products[ProductName])

D. TopProducts = CALCULATE(Products, TOPN(5))

Solution

1. Step 1: Identify correct DAX function for top rows

   TOPN returns the top N rows based on a column, here top 5 by sales.

2. Step 2: Check syntax correctness

   TopProducts = TOPN(5, Products, Products[Sales], DESC) uses TOPN(5, Products, Products[Sales], DESC) which is correct syntax for top 5 products by sales.

3. Final Answer:

   TopProducts = TOPN(5, Products, Products[Sales], DESC) -> Option A

4. Quick Check:

   TOPN syntax = TopProducts = TOPN(5, Products, Products[Sales], DESC) [OK]

Hint: Use TOPN for top rows with order [OK]

Common Mistakes:

• Using FILTER instead of TOPN for top rows
• Incorrect CALCULATE usage for tables
• Missing order direction in TOPN

3. Given the calculated table:

HighSales = FILTER(Sales, Sales[Amount] > 1000)

What will HighSales contain?

medium

A. Sum of sales Amount greater than 1000

B. All sales records where Amount is less than 1000

C. Error because FILTER cannot be used in calculated tables

D. All sales records where Amount is greater than 1000

Solution

1. Step 1: Understand FILTER function in calculated tables

   FILTER returns a table with rows meeting the condition, here Amount > 1000.

2. Step 2: Analyze the condition

   It keeps only sales records where Amount is greater than 1000.

3. Final Answer:

   All sales records where Amount is greater than 1000 -> Option D

4. Quick Check:

   FILTER with condition = filtered rows [OK]

Hint: FILTER returns rows matching condition [OK]

Common Mistakes:

• Thinking FILTER returns aggregated values
• Confusing greater than with less than
• Believing FILTER cannot create tables

4. You wrote this calculated table:

RecentSales = FILTER(Sales, Sales[Date] >= TODAY() - 30)

But it gives an error. What is the likely problem?

medium

A. Sales table cannot be filtered in calculated tables

B. FILTER requires aggregation functions inside

C. TODAY() cannot be used in calculated tables because it is dynamic

D. The date comparison operator >= is invalid in DAX

Solution

1. Step 1: Understand TODAY() in calculated tables

   TODAY() is dynamic and calculated tables are static, so TODAY() causes error.

2. Step 2: Check other options

   FILTER works without aggregation, >= is valid, and Sales can be filtered.

3. Final Answer:

   TODAY() cannot be used in calculated tables because it is dynamic -> Option C

4. Quick Check:

   Dynamic functions disallowed in calculated tables [OK]

Hint: Avoid dynamic functions like TODAY() in calculated tables [OK]

Common Mistakes:

• Thinking FILTER needs aggregation
• Misunderstanding comparison operators
• Assuming all functions work in calculated tables

5. You want to create a calculated table CustomerSalesSummary that shows each customer and their total sales amount, but only for customers with sales above 5000. Which DAX expression correctly achieves this?

hard

A. CustomerSalesSummary = SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", FILTER(Sales[Amount] > 5000))

B. CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000)

C. CustomerSalesSummary = CALCULATETABLE(Sales, Sales[Amount] > 5000)

D. CustomerSalesSummary = SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])) WHERE TotalSales > 5000

Solution

1. Step 1: Summarize sales by customer

   SUMMARIZE groups Sales by CustomerID and calculates total sales per customer.

2. Step 2: Filter summarized table by total sales > 5000

   FILTER applies condition on the summarized table's TotalSales column.

3. Step 3: Check syntax correctness

   CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000) correctly nests FILTER around SUMMARIZE with proper syntax.

4. Final Answer:

   CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000) -> Option B

5. Quick Check:

   SUMMARIZE then FILTER = CustomerSalesSummary = FILTER(SUMMARIZE(Sales, Sales[CustomerID], "TotalSales", SUM(Sales[Amount])), [TotalSales] > 5000) [OK]

Hint: Use SUMMARIZE then FILTER for grouped filtered tables [OK]

Common Mistakes:

• Placing FILTER inside SUMMARIZE incorrectly
• Using WHERE clause which is invalid in DAX
• Applying CALCULATETABLE without grouping