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.

Practice

1. What does the DAX expression Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price]) calculate?

easy

A. The division of quantity by price

B. The sum of quantity and price

C. The difference between quantity and price

D. The total sales amount by multiplying quantity and price

Solution

1. Step 1: Understand the multiplication operator in DAX

   The asterisk (*) symbol multiplies two numbers or columns in DAX.

2. Step 2: Analyze the expression components

   SUMX(Sales, Sales[Quantity] * Sales[Price]) iterates over Sales table, multiplies Quantity and Price row-by-row, and sums the results.

3. Final Answer:

   The total sales amount by multiplying quantity and price -> Option D

4. Quick Check:

   Quantity * Price = Total Sales [OK]

Hint: Multiply quantity and price to get total sales [OK]

Common Mistakes:

• Confusing multiplication with addition
• Using division instead of multiplication
• Ignoring the column context in calculation

2. Which of the following is the correct syntax to create a new measure that subtracts Discount from TotalPrice in DAX?

easy

A. NewMeasure = TotalPrice + Discount

B. NewMeasure := TotalPrice - Discount

C. NewMeasure = TotalPrice - Discount

D. NewMeasure - TotalPrice - Discount

Solution

1. Step 1: Recall the correct assignment operator in DAX

   DAX uses the equals sign (=) to assign expressions to measures.

2. Step 2: Check the arithmetic operator for subtraction

   The minus sign (-) subtracts one value from another.

3. Final Answer:

   NewMeasure = TotalPrice - Discount -> Option C

4. Quick Check:

   Use '=' for assignment and '-' for subtraction [OK]

Hint: Use '=' to assign and '-' to subtract in DAX [OK]

Common Mistakes:

• Using ':=' instead of '=' for measure assignment
• Using '+' instead of '-' for subtraction
• Missing the assignment operator

3. Given the DAX measure:
Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])
and the Sales table has Revenue = 1000 and Cost = 700,
what is the value of Profit?

medium

A. 1700

B. 300

C. 700

D. 1000

Solution

1. Step 1: Calculate the sum of Revenue

   SUM(Sales[Revenue]) = 1000 as given.

2. Step 2: Calculate the sum of Cost and subtract

   SUM(Sales[Cost]) = 700, so Profit = 1000 - 700 = 300.

3. Final Answer:

   300 -> Option B

4. Quick Check:

   Revenue - Cost = 300 [OK]

Hint: Subtract total cost from total revenue to get profit [OK]

Common Mistakes:

• Adding instead of subtracting
• Using individual row values instead of sums
• Confusing Revenue and Cost columns

4. Identify the error in this DAX measure:
NetAmount = SUM(Sales[Amount]) + - SUM(Sales[Discount])

medium

A. No error, the syntax is correct

B. Missing parentheses around SUM functions

C. SUM function cannot be used with columns

D. Incorrect use of '+ -' operators together

Solution

1. Step 1: Analyze the operator usage

   The '+ -' uses unary minus after addition, which is valid in DAX.

2. Step 2: Verify the expression

   SUM(Sales[Amount]) + (-SUM(Sales[Discount])) correctly computes net amount as Amount minus Discount.

3. Final Answer:

   No error, the syntax is correct -> Option A

4. Quick Check:

   SUM(Amount) + -SUM(Discount) = Net Amount [OK]

Hint: '+ -' is valid unary minus for subtraction [OK]

Common Mistakes:

• Thinking '+ -' is invalid syntax
• Assuming parentheses are always required around SUM
• Thinking SUM can't be used on columns

5. You want to create a measure that calculates the average price after applying a 10% discount on the Price column. Which DAX expression correctly applies this?

hard

A. AvgDiscountedPrice = AVERAGEX(Sales, Sales[Price] * 0.9)

B. AvgDiscountedPrice = AVERAGE(Sales[Price] * 0.9)

C. AvgDiscountedPrice = SUM(Sales[Price * 0.9]) * 0.9 / COUNT(Sales[Price])

D. AvgDiscountedPrice = SUM(Sales[Price] * 0.9) / COUNT(Sales[Price])

Solution

1. Step 1: Understand the requirement

   We need the average of prices after applying a 10% discount on each price (row context).

2. Step 2: Analyze each option

   Averaging requires row-by-row computation for expressions: AVERAGEX(Sales, Sales[Price] * 0.9) correctly iterates and averages discounted prices. A, C, and D have syntax errors (aggregate on expression without iterator or invalid column reference).

3. Final Answer:

   AvgDiscountedPrice = AVERAGEX(Sales, Sales[Price] * 0.9) -> Option A

4. Quick Check:

   Use AVERAGEX for average of row expressions [OK]

Hint: Apply discount inside AVERAGEX to get correct result [OK]

Common Mistakes:

• Using AVERAGE on expression without iterator
• Using SUM and COUNT instead of AVERAGEX
• Invalid column references like [Price * 0.9]