Basic arithmetic in DAX in Power BI - Step-by-Step Guide

Introduction

Basic arithmetic in DAX lets you do simple math like adding, subtracting, multiplying, and dividing numbers in your Power BI data. This helps you create new insights by calculating totals, differences, or ratios directly in your reports.

When you want to calculate total sales by adding amounts from different columns

When you need to find the difference between this year's and last year's revenue

When you want to multiply quantity by price to get total cost

When you want to divide total profit by total sales to get profit margin

When you want to create a new column that shows a simple calculation based on existing data

Steps

Step 1: Open

- Power BI Desktop

The Power BI interface with your data model and report canvas appears

Step 2: Click

- Modeling tab

Modeling options appear including New Measure and New Column buttons

Step 3: Click

- New Measure button

A formula bar opens at the top where you can type your DAX formula

Step 4: Type

- formula bar

The formula bar shows your typed DAX expression

💡 Use simple operators like + for add, - for subtract, * for multiply, / for divide

Step 5: Press

- Enter key

The new measure is created and appears in the Fields pane

Step 6: Drag

- new measure from Fields pane to report canvas

A card or visual shows the calculated result based on your arithmetic formula

Before vs After

Before

Fields pane shows only raw data columns like SalesAmount and Quantity

After

Fields pane shows a new measure named TotalSales with the formula [SalesAmount] * [Quantity], and the report shows the total sales value

Settings Reference

New Measure

📍 Modeling tab

To create a calculation that aggregates or computes values dynamically

Default: No measure created

New Column

📍 Modeling tab

To add a new column with calculated values for each row

Default: No calculated column

Common Mistakes

Using incorrect operators like 'x' instead of '*' for multiplication

DAX only recognizes standard math operators: +, -, *, /

Use '*' for multiplication in your DAX formulas

Typing column names without brackets

DAX requires column names to be enclosed in square brackets to identify them

Always write column names like [SalesAmount] inside square brackets

Creating a measure when a calculated column is needed for row-level calculation

Measures calculate aggregated results, not row-by-row values

Use New Column for row-level arithmetic calculations

Summary

Basic arithmetic in DAX uses simple math operators to create new calculations.

You can create measures or calculated columns to add, subtract, multiply, or divide data.

Remember to use correct syntax: brackets for columns and standard math symbols.

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]