KPI visual in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales for Current Year

You have a sales table with a column SalesAmount and a date column OrderDate. You want to create a measure that calculates the total sales for the current year only.

Which DAX measure will correctly calculate this?

A Total Sales CY = SUMX(FILTER(Sales, YEAR(Sales[OrderDate]) = YEAR(TODAY())), Sales[SalesAmount])

B Total Sales CY = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, YEAR(Sales[OrderDate]) = YEAR(TODAY())))

C Total Sales CY = CALCULATE(SUM(Sales[SalesAmount]), YEAR(Sales[OrderDate]) = YEAR(TODAY()))

D Total Sales CY = SUM(Sales[SalesAmount])

💡 Hint

Use CALCULATE with FILTER to apply a row context filter on the date column.

Option B uses CALCULATE with FILTER to correctly filter rows where the year of OrderDate matches the current year. Option B tries to use a boolean expression directly inside CALCULATE, which is invalid. Option B uses SUMX with FILTER, which works but is less efficient. Option B sums all sales without filtering.

❓ visualization

intermediate

Best Practice for KPI Visual Color Usage

You want to create a KPI visual in Power BI that shows sales growth. Which color usage follows best practices for accessibility and clarity?

A Use red for positive growth and green for negative growth.

B Use random bright colors for each KPI to attract attention.

C Use only blue shades for all KPI values to keep it simple.

D Use green for positive growth, red for negative growth, and ensure color contrast meets accessibility standards.

💡 Hint

Think about common color meanings and accessibility.

Green usually means good or positive, red means bad or negative. Ensuring color contrast helps users with visual impairments. Option D follows these best practices. Option D reverses common meanings. Option D lacks differentiation. Option D can confuse users and reduce clarity.

❓ data_modeling

advanced

Modeling Data for KPI Visual with Multiple Date Filters

You have sales data and a separate date table. You want to create a KPI visual that shows sales for the current month and compares it to the previous month. What is the best data modeling approach?

A Create a relationship between sales and date tables on the date column, mark the date table as a date table, and use time intelligence DAX functions.

B Keep sales and date tables unlinked and use LOOKUPVALUE in measures to get dates.

C Merge sales and date tables into one big table to simplify filtering.

D Create multiple disconnected date tables for each KPI and use USERELATIONSHIP in measures.

💡 Hint

Think about how Power BI uses relationships and date tables for time intelligence.

Option A is best practice: a single date table linked to sales allows use of built-in time intelligence functions like SAMEPERIODLASTMONTH. Option A is inefficient and error-prone. Option A can cause data duplication and performance issues. Option A complicates the model unnecessarily.

🔧 Formula Fix

advanced

Identify the Error in KPI Measure Calculation

Given this DAX measure for a KPI visual:

Sales Growth % = DIVIDE(SUM(Sales[SalesAmount]) - SUM(Sales[SalesAmount]), SUM(Sales[SalesAmount]), 0)

What is the main issue causing incorrect KPI output?

A The numerator subtracts the same sum from itself, always resulting in zero.

B The measure lacks a FILTER to limit data to current and previous periods.

C SUM function is used incorrectly with two arguments.

D The DIVIDE function is missing the alternate result parameter.

💡 Hint

Look carefully at the subtraction in the numerator.

The numerator subtracts SUM(Sales[SalesAmount]) from itself, so it always equals zero, making the growth always zero. Option A is incorrect because DIVIDE has a third argument (0) as alternate result. Option A is wrong because SUM does not accept two arguments, but here the second argument is inside DIVIDE, so syntax is valid. Option A is a modeling suggestion but not the main error.

🎯 Scenario

expert

Designing a KPI Visual for Multi-Category Sales Growth

You need to create a KPI visual that shows sales growth for multiple product categories side by side. The data model has a sales table and a product category table. How should you design the KPI visual and measures to best support this?

A Create a calculated column in sales table for growth and use a card visual for each category.

B Create separate KPI visuals for each category with hardcoded filters in each visual.

C Create a measure that calculates sales growth filtered by the selected category, then use a matrix visual with categories as rows and the KPI measure as values.

D Use a slicer to select one category at a time and show a single KPI card visual.

💡 Hint

Think about dynamic filtering and scalability for multiple categories.

Option C allows dynamic calculation of sales growth per category and displays them side by side in a matrix, which is scalable and user-friendly. Option C is inefficient and hard to maintain. Option C uses calculated columns which are static and not recommended for dynamic KPIs. Option C limits user to one category at a time, reducing insight.