DIVIDE
One number divided by another; BLANK, or an alternate result, when the divisor is zero or blank.
Group: Math. Compatible with DAX.
Syntax
DIVIDE ( <numerator>, <denominator> [, <alternateresult>] )| Parameter | Type | Description |
|---|---|---|
numerator | Number | The number to divide. |
denominator | Number | The number to divide by. |
alternateresult | Value optional | What to give back when the denominator is zero or blank. When left out, BLANK. |
Returns
Number. The numerator divided by the denominator; the alternate result, or BLANK, when there is nothing to divide by.
Remarks
- The
/operator divides by zero without complaint and gives Infinity, or NaN for 0 / 0. DIVIDE gives BLANK instead, which a visual simply leaves empty. - A blank denominator counts as zero:
DIVIDE ( 10, BLANK () )is BLANK,DIVIDE ( 10, BLANK (), 0 )is 0. - A blank numerator gives BLANK, even when an alternate result is given:
DIVIDE ( BLANK (), 0, 0 )is blank, not 0. A product with no sales therefore stays out of a visual. - Use it for every ratio whose denominator can be empty: a share of a total, a growth rate against a year without sales, a price per liter.
Compared with DAX
DAX wants the alternate result to be a constant. OpenBI also accepts an expression there; keep it a constant in a model that must run on Power BI as well.
Examples
Zero and blank denominators
The alternate result is used for a zero denominator, but a blank numerator stays blank.
EVALUATE
ROW (
"Ten by four", DIVIDE ( 10, 4 ),
"By zero", DIVIDE ( 10, 0 ),
"By zero, or -1", DIVIDE ( 10, 0, -1 ),
"Blank by zero, or 0", DIVIDE ( BLANK (), 0, 0 )
)| Ten by four | By zero | By zero, or -1 | Blank by zero, or 0 |
|---|---|---|---|
| 2.5 | (blank) | -1 | (blank) |
DIVIDE against the / operator
South has a target of 0 and East none at all: the operator gives Infinity for both.
EVALUATE
VAR Targets =
DATATABLE (
"Shop", STRING, "Liters", INTEGER, "Target", INTEGER,
{ { "North", 120, 100 }, { "South", 80, 0 }, { "East", 45, BLANK () } }
)
RETURN
ADDCOLUMNS (
Targets,
"With /", [Liters] / [Target],
"DIVIDE", DIVIDE ( [Liters], [Target] ),
"DIVIDE, or 0", DIVIDE ( [Liters], [Target], 0 )
)
ORDER BY [Shop]| Shop | Liters | Target | With / | DIVIDE | DIVIDE, or 0 |
|---|---|---|---|---|---|
| East | 45 | (blank) | Infinity | (blank) | 0 |
| North | 120 | 100 | 1.2 | 1.2 | 1.2 |
| South | 80 | 0 | Infinity | (blank) | 0 |
Each product's share of all liters
EVALUATE
ADDCOLUMNS (
VALUES ( Products[Product] ),
"Share", DIVIDE ( [Liters sold], CALCULATE ( [Liters sold], ALL ( Products ) ) )
)
ORDER BY [Product]| Products[Product] | Share |
|---|---|
| Cider | 0.166664913701526 |
| IPA | 0.183720341905702 |
| Lettøl | 0.0936520703717707 |
| Pils | 0.555962674021001 |