Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX DIVIDE


Share

The DIVIDE Function

The DIVIDE function divides one number by another.

When you divide by zero, it returns a blank instead of an error.

Syntax

DIVIDE(numerator, denominator [, alternateresult])
ArgumentMeaning
numeratorThe number to divide.
denominatorThe number to divide by.
alternateresultOptional. The value to return when the denominator is zero or blank. If you leave it out, DIVIDE returns a blank.

This page uses the Poke Mart model described in DAX Introduction, with the measures Total Sales from DAX Measures, and Total Cost and Order Count from DAX Aggregation Functions.


Why Not Just Use / ?

DAX has a division operator, /, just like Excel.

It works fine until the denominator is zero:

FormulaResult
10 / 25
10 / 0Infinity
0 / 0NaN (not a number)
DIVIDE(10, 2)5
DIVIDE(10, 0)(blank)
DIVIDE(10, 0, 0)0

In Excel, dividing by zero gives the #DIV/0! error.

DAX returns Infinity or NaN instead.

In a report, Infinity and NaN look like mistakes to the reader.

A blank is better: by default, visuals leave out the rows where the measures are blank, so the reader only sees rows with data.

In a measure, the denominator is often another measure.

A measure can be zero or blank for some filters, for example for a product that has no sales in a given month. So Microsoft recommends:

  • Use DIVIDE when the denominator is a measure or an expression that could be zero or blank.
  • Use / when you divide by a constant, like [Total Sales] / 1000. There, the division can never fail.


Profit

First, one more measure that you need for the margin.

Profit is the sales minus the costs.

There is no division, so a minus sign is all you need:

Example

Profit = [Total Sales] - [Total Cost]
MeasureResult
Profit4,292,195

Profit uses two measures you already have: Total Sales from DAX Measures and Total Cost from DAX Aggregation Functions.

As the naming convention says, the measures are written without a table name.


Margin %

The profit margin is the share of the sales that is profit: Profit divided by Total Sales.

Example

Margin % = DIVIDE([Profit], [Total Sales])

Format the measure as a percentage: select it, and on the Measure tools tab, set Format to Percentage and the number of decimal places to 2.

MeasureResult
Margin %45.58%

Of every 100 in sales, about 46 is profit.


Avg Order Value

The average order value is the sales divided by the number of orders:

Example

Avg Order Value = DIVIDE([Total Sales], [Order Count])

Format this measure as Decimal number with 2 decimal places and a thousands separator.

MeasureResult
Avg Order Value3,436.79

Each row of Sales is one order, so AVERAGE(Sales[Amount]) returns the same number.

Building the measure from Total Sales and Order Count makes its meaning clear, and it reuses two measures you already trust.


The Results by Category

Put Category and the measures in a table visual:

CategoryTotal SalesTotal CostProfitMargin %Order CountAvg Order Value
Balls4,447,2002,477,5601,969,64044.29%1,0354,296.81
Medicine4,413,6002,386,5402,027,06045.93%1,1103,976.22
Status Healers556,000260,505295,49553.15%595934.45
Total9,416,8005,124,6054,292,19545.58%2,7403,436.79

Status Healers are a small part of the sales, but they have the highest margin: 53.15%.

Balls sell the most, but with the lowest margin.

Notice that the Total row of Margin % is not the average of the three percentages.

It is 4,292,195 divided by 9,416,800, calculated over all the rows.

Measures always work this way.

By Year

Replace Category with Year from the Date table:

YearTotal SalesProfitMargin %Avg Order Value
20244,393,2001,999,57045.52%3,442.95
20255,023,6002,292,62545.64%3,431.42
Total9,416,8004,292,19545.58%3,436.79

Sales grew by 630,400 from 2024 to 2025, and the margin stayed almost the same.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.