Pivoting columns in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate total sales per product category using pivoted columns

You have a table with sales data pivoted by product categories as columns: Electronics, Clothing, and Furniture. Each column shows sales amount for that category per month.

Which DAX measure correctly calculates the total sales across all categories for each month?

Power BI

SalesData = DATATABLE(
    "Month", STRING,
    "Electronics", INTEGER,
    "Clothing", INTEGER,
    "Furniture", INTEGER,
    {
        {"Jan", 1000, 500, 300},
        {"Feb", 1200, 600, 400}
    }
)

// Choose the correct measure:

A Total Sales = CALCULATE(SUM(SalesData[Electronics] + SalesData[Clothing] + SalesData[Furniture]))

B Total Sales = SUMX(SalesData, SalesData[Electronics] * SalesData[Clothing] * SalesData[Furniture])

C Total Sales = SUM(SalesData[Electronics] + SalesData[Clothing] + SalesData[Furniture])

D Total Sales = SUM(SalesData[Electronics]) + SUM(SalesData[Clothing]) + SUM(SalesData[Furniture])

💡 Hint

Think about how to add values from multiple columns correctly.

Option D sums each category column separately and adds them, which correctly totals sales.

Option D multiplies values, which is incorrect.

Option D tries to sum an expression inside SUM, which is invalid syntax.

Option D sums the addition inside SUM, which causes a type error.

❓ visualization

intermediate

Best visualization for pivoted monthly sales data

You have monthly sales data pivoted by product categories as columns. You want to show trends over time for each category clearly.

Which visualization type is best suited for this pivoted data?

A Table showing raw pivoted sales data

B Stacked column chart showing months on X-axis and sales on Y-axis with categories stacked

C Line chart with months on X-axis and separate lines for each product category

D Pie chart showing total sales per category for all months combined

💡 Hint

Think about how to show trends over time for multiple categories.

Line charts are best for showing trends over time with multiple categories as separate lines.

Stacked columns can show totals but make it harder to compare categories individually over time.

Pie charts do not show trends over time.

Tables show raw data but are less visual and harder to interpret trends.

❓ data_modeling

advanced

Handling pivoted columns in data model for flexible analysis

You receive sales data with product categories as separate columns (pivoted). You want to create a data model that allows easy filtering and aggregation by category.

What is the best approach to handle this pivoted data in your model?

A Unpivot the category columns into two columns: Category and Sales, then relate to a Category table

B Keep the pivoted columns as is and create measures summing each column separately

C Create calculated columns that concatenate all category sales into one string per row

D Remove all category columns and use only total sales column

💡 Hint

Think about how to make filtering by category easier and more scalable.

Unpivoting converts columns into rows, creating a normalized structure that supports filtering and aggregation by category.

Keeping pivoted columns limits flexibility and requires many measures.

Concatenating sales into strings prevents numeric analysis.

Removing category columns loses detail.

🔧 Formula Fix

advanced

Identify error in DAX measure summing pivoted columns

Given a table with pivoted sales columns: Electronics, Clothing, Furniture, a developer wrote this DAX measure:

Total Sales = SUM(SalesData[Electronics] + SalesData[Clothing] + SalesData[Furniture])

What error will this measure cause when used in a report?

A SyntaxError due to invalid use of SUM with addition inside

B TypeError because SUM expects a single column but receives an expression

C Returns incorrect total because it sums only Electronics column

D No error, returns correct total sales

💡 Hint

Consider what the SUM function expects as input.

SUM expects a single column reference. Adding columns inside SUM creates an expression, causing a type error.

It is not a syntax error but a semantic error at runtime.

Option B is incorrect because it does not sum only one column.

Option B is incorrect because the measure fails.

🧠 Conceptual

expert

Why unpivoting columns improves BI report performance and flexibility

In a BI project, you receive data with many product categories as separate columns (pivoted). You decide to unpivot these columns into rows before loading into the model.

What are the main benefits of unpivoting pivoted columns in this context?

A Unpivoting creates a normalized structure that simplifies filtering, aggregation, and reduces the number of measures needed

B Unpivoting reduces data size by removing duplicate values and improves compression

C Unpivoting automatically creates calculated columns for each category, improving calculation speed

D Unpivoting merges all category sales into a single column, which improves visualization options

💡 Hint

Think about how data structure affects filtering and measure creation.

Unpivoting creates a normalized table with category and value columns, making filtering and aggregation easier and reducing the need for many separate measures.

It does not necessarily reduce data size or create calculated columns automatically.

Merging sales into a single column without category info reduces analysis options.