Basic arithmetic in DAX in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales with a Simple Arithmetic Expression

You have a table named Sales with columns Quantity and UnitPrice. You want to create a measure that calculates the total sales amount by multiplying quantity by unit price and summing over all rows.

Which DAX expression correctly calculates this total sales amount?

A Total Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

B Total Sales = SUM(Sales[Quantity]) * SUM(Sales[UnitPrice])

C Total Sales = SUM(Sales[Quantity] + Sales[UnitPrice])

D Total Sales = SUMX(Sales, Sales[Quantity] + Sales[UnitPrice])

💡 Hint

Think about multiplying quantity and price for each row, then adding all results.

Option A uses SUMX to multiply Quantity and UnitPrice row by row, then sums the results. Option A multiplies total quantities by total prices, which is incorrect. Options C and D add quantities and prices, not multiply.

❓ visualization

intermediate

Choose the Best Visualization for Showing Sales Growth Percentage

You have calculated a measure Sales Growth % that shows the percentage increase in sales compared to the previous year. You want to display this on a dashboard for easy comparison across years.

Which visualization type is best suited to clearly show the sales growth percentage over multiple years?

A Pie Chart showing sales distribution by year

B Line Chart showing Sales Growth % over years

C Stacked Bar Chart showing total sales per year

D Table showing sales and sales growth % side by side

💡 Hint

Think about how to best show trends over time.

A line chart is best for showing trends like percentage growth over time. Stacked bar charts and pie charts are better for absolute values or parts of a whole. Tables show numbers but are less visual for trends.

❓ data_modeling

advanced

Create a Calculated Column for Profit Margin Percentage

You have a Sales table with columns Revenue and Cost. You want to add a calculated column that shows the profit margin percentage for each sale.

Which DAX formula correctly creates this calculated column?

A Profit Margin % = (Sales[Revenue] - Sales[Cost]) / Sales[Revenue]

B Profit Margin % = SUM(Sales[Revenue]) - SUM(Sales[Cost]) / SUM(Sales[Revenue])

C Profit Margin % = DIVIDE(Sales[Revenue] - Sales[Cost], Sales[Revenue])

D Profit Margin % = Sales[Revenue] - Sales[Cost] / Sales[Revenue]

💡 Hint

Use a function that safely handles division by zero.

Option C uses DIVIDE which safely divides and handles division by zero. Option C is missing DIVIDE and may cause errors if revenue is zero. Option C uses aggregation functions which are not valid in calculated columns. Option C has incorrect operator precedence causing wrong calculation.

🔧 Formula Fix

advanced

Identify the Error in This DAX Measure for Average Price

Consider this DAX measure intended to calculate the average unit price:

Average Price = SUM(Sales[UnitPrice]) / COUNT(Sales[UnitPrice])

What is the main problem with this measure?

A It will cause a syntax error due to missing parentheses

B It sums prices instead of summing total sales amount

C It divides sum of prices by count of prices, which is correct

D It should use AVERAGE function instead for better performance and accuracy

💡 Hint

Think about built-in functions for averages.

While the formula mathematically calculates average, using AVERAGE(Sales[UnitPrice]) is simpler, more readable, and optimized. The given formula works but is not best practice. There is no syntax error.

🧠 Conceptual

expert

Understanding Evaluation Context in Arithmetic Measures

You create a measure Total Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost]). When you place this measure in a table visual grouped by Region, the profit is calculated per region. But when you use it in a card visual, it shows the total profit for all regions.

Why does the same measure show different results in these visuals?

A Because the evaluation context changes based on the visual's filters and grouping

B Because the data model automatically filters the measure only in table visuals

C Because the measure is recalculated incorrectly in card visuals

D Because the measure uses different aggregation functions in each visual

💡 Hint

Think about how filters and grouping affect calculations.

The evaluation context in DAX changes depending on the visual. In a table grouped by region, the measure calculates profit per region. In a card visual with no grouping, it calculates total profit. This is normal behavior due to context.