Why advanced visuals reveal deeper insights in Power BI - Challenge Your Understanding

Challenge - 5 Problems

❓ visualization

intermediate

Identify the best visual for showing trends over time

You have monthly sales data for 3 years. Which visual will best show sales trends clearly?

A A clustered bar chart with months on the x-axis and sales on the y-axis

B A line chart with months on the x-axis and sales on the y-axis

C A stacked area chart with years on the x-axis and sales on the y-axis

D A pie chart showing total sales per year

💡 Hint

Think about which chart type best shows continuous data changes over time.

Line charts are best for showing trends over time because they connect data points smoothly, making it easy to see increases or decreases.

❓ dax_lod_result

intermediate

Calculate average sales per customer segment

Given a sales table with CustomerSegment and SalesAmount columns, which DAX measure correctly calculates the average sales per segment?

Power BI

Measure = AVERAGEX(VALUES(Sales[CustomerSegment]), CALCULATE(SUM(Sales[SalesAmount])))

A Measure = AVERAGE(Sales[SalesAmount])

B Measure = CALCULATE(AVERAGE(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[CustomerSegment]))

C Measure = AVERAGEX(VALUES(Sales[CustomerSegment]), CALCULATE(SUM(Sales[SalesAmount])))

D Measure = SUM(Sales[SalesAmount]) / COUNTROWS(Sales)

💡 Hint

Think about how to calculate average sales per unique segment, not overall average.

Option C uses AVERAGEX over unique segments, summing sales per segment, then averaging those sums. This correctly calculates average sales per segment.

❓ data_modeling

advanced

Optimize a data model for faster report loading

You have a large sales dataset with many columns, but your report only uses a few. What is the best way to optimize the model for performance?

A Remove unused columns and create relationships only for needed tables

B Keep all columns and create many-to-many relationships for flexibility

C Duplicate tables to avoid complex relationships

D Use calculated columns for all measures to speed up calculations

💡 Hint

Think about reducing data size and simplifying relationships.

Removing unused columns reduces data size and memory use. Creating only necessary relationships simplifies the model and speeds up queries.

🎯 Scenario

advanced

Choosing the right visual to compare multiple categories and subcategories

You want to compare sales across product categories and their subcategories in one visual. Which visual type best reveals detailed insights?

A A scatter plot with categories on x-axis and sales on y-axis

B A stacked bar chart showing categories and subcategories stacked

C A pie chart for each category showing subcategory sales

D A matrix visual with categories as rows and subcategories as columns showing sales values

💡 Hint

Think about how to show detailed breakdowns in a clear, tabular way.

A matrix visual allows easy comparison of categories and subcategories side by side with exact values, revealing deeper insights.

🔧 Formula Fix

expert

Identify the error in this DAX measure for cumulative sales

What error does this DAX measure produce?

Measure = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date])))

Power BI

Measure = CALCULATE(SUM(Sales[SalesAmount]), FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date])))

A No error; measure calculates cumulative sales correctly

B MAX function causes a context transition error inside FILTER

C SyntaxError due to missing closing parenthesis

D ALL function removes all filters causing incorrect cumulative calculation

💡 Hint

Check if the DAX syntax is complete and if the functions are used correctly.

The measure correctly calculates cumulative sales by removing date filters and summing sales up to the current max date in context. There is no syntax or runtime error.