Arithmetic Operations in DAX: Calculate Profit with Inclusive and Exclusive Tax

What Are Arithmetic Operations in DAX?

Arithmetic operations allow us to perform mathematical calculations on numeric values
in DAX. These operations are useful for calculating sales, tax, profit, discounts,
margins, and other business metrics.

The main arithmetic operators are:

In this example, we will use arithmetic operations to calculate
profit from sales amounts and product costs.

Understanding the Tax Variable

Query

// Arithmetic Operations

DEFINE
    VAR Tax = 18

Step-by-step explanation

Step 1: DEFINE
DEFINE is used to declare variables that can be used
in one or more DAX queries.

Step 2: VAR Tax = 18
This creates a variable named Tax and assigns it the value
18.

The value 18 represents an 18% tax rate.
To use it in a calculation, we convert it into a decimal by dividing it by 100.

Tax / 100

18 / 100 = 0.18

1. Calculate Profit Using Inclusive of Tax

In this scenario, the Sales Amount already includes tax.
Therefore, we first calculate the Base Amount by removing
the tax component from the sales amount.

Query

// Scenario 01: Calculate Profit using Inclusive of Tax

EVALUATE
    ADDCOLUMNS(
        Sales,
        "Base Amount", Sales[Sales Amount] / (1 + (Tax/100)),
        "Profit", Sales[Sales Amount] / (1 + (Tax/100)) - Sales[Total Product Cost]
    )

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression.
In this case, the result is a table containing the original Sales columns
and the newly calculated columns.

Step 2: ADDCOLUMNS
ADDCOLUMNS adds calculated columns to an existing table.
Here, the source table is Sales.

Step 3: Calculate the Base Amount
The expression:

Sales[Sales Amount] / (1 + (Tax/100))

First, we convert the tax percentage into a decimal:

Tax / 100
18 / 100 = 0.18

Next, we add 1 to the tax rate:

1 + 0.18 = 1.18

Finally, we divide the inclusive sales amount by 1.18
to calculate the amount before tax.

Step 4: Calculate Profit
The expression:

Sales[Sales Amount] / (1 + (Tax/100)) - Sales[Total Product Cost]

This formula first calculates the Base Amount and then subtracts
Total Product Cost.

Profit = Base Amount – Total Product Cost

Example calculation

Suppose a sales amount is 1,180 and the total product cost is
700.

What will you get?

The query returns the original Sales table along with two new columns:

The example above is only for illustration. Your actual result will contain
the data available in your Sales table.

2. Calculate Profit Using Exclusive Tax

In this scenario, we calculate the tax separately from the sales amount.
The sales amount is treated as the amount before tax.

Query

// Scenario 02: Calculate Profit using Exclusive Tax

EVALUATE
    ADDCOLUMNS(
        Sales,
        "Exclusive Tax", Sales[Sales Amount] * (Tax/100),
        "Profit", Sales[Sales Amount] - Sales[Total Product Cost]
    )

Step-by-step explanation

Step 1: EVALUATE
Returns the result of the table expression.

Step 2: ADDCOLUMNS
Adds calculated columns to the Sales table.

Step 3: Calculate Exclusive Tax
The expression:

Sales[Sales Amount] * (Tax/100)

First, we convert the tax percentage into a decimal:

Tax / 100
18 / 100 = 0.18

Next, we multiply the sales amount by the tax rate.

Exclusive Tax = Sales Amount × Tax Rate

Step 4: Calculate Profit
The expression:

Sales[Sales Amount] - Sales[Total Product Cost]

This calculates profit by subtracting the total product cost from the sales amount.

Profit = Sales Amount – Total Product Cost

Example calculation

Suppose a sales amount is 1,000 and the total product cost is
700.

What will you get?

The query returns the original Sales table along with two new columns:

The example above is only for illustration. Your actual result will contain
the data available in your Sales table.

3. Handle Zero Division Errors Using DIVIDE

Division by zero can cause errors in calculations. DAX provides the
DIVIDE function to handle this situation safely.

Query

// Scenario 03: Zero Division Error

EVALUATE
    VAR TotalSales = 1000
    VAR TotalTax = 0
    VAR SalesTaxRatio = DIVIDE(TotalSales, TotalTax, 0)
RETURN
    ROW(
        "Sales Tax Ratio", SalesTaxRatio
    )

Step-by-step explanation

Step 1: EVALUATE
Returns the result of the table expression.

Step 2: VAR TotalSales = 1000
Creates a variable named TotalSales with the value
1000.

Step 3: VAR TotalTax = 0
Creates a variable named TotalTax with the value
0.

Step 4: VAR SalesTaxRatio = DIVIDE(TotalSales, TotalTax, 0)
The DIVIDE function calculates the ratio safely.

DIVIDE(<Numerator>, <Denominator>, <Alternate Result>)

In this example:

Since the denominator is 0, DAX returns the alternate result,
which is 0.

Step 5: RETURN
RETURN specifies the expression that should be returned
after the variables are defined.

Step 6: ROW
ROW creates a single-row table containing the calculated value.

ROW(
    "Sales Tax Ratio", SalesTaxRatio
)

What will you get?

Understanding the Difference

Important Notes

1. Inclusive and exclusive tax are different calculations

Inclusive tax means the sales amount already contains tax.
Exclusive tax means the tax is calculated separately from the sales amount.

2. Arithmetic operations follow mathematical rules

DAX uses standard arithmetic operators such as
+, –, *, and /.

3. DIVIDE is useful for safe division

When the denominator may be zero, use DIVIDE with an
alternate result to avoid invalid calculations.

4. Variables make calculations easier to understand

Variables such as Tax, TotalSales,
and TotalTax make the query easier to read and maintain.

5. ADDCOLUMNS creates calculated columns

ADDCOLUMNS returns a new table expression with the
calculated columns added to the original table.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist