Power BI DAX: DIVIDE

We’re taking on the simple yet incredibly useful DIVIDE DAX function today. This function has a cool piece in it that accounts for when your denominator is a 0. When you divide numbers in Excel, you’ll get at #DIV/0! error when your denominator is a 0 (because nothing can be divided by 0) but this DAX function lets you account for that.

We’ll learn the basics of the DIVIDE function with a very simple dataset. If you’d like a copy of the Power BI report I use in the below video and written instructions, you can download it here:

Video:

Written Instructions:

We’re starting with a report with just a small table on it where we can see our data. We just have a few States and a Numerator and Denominator for each.

The Numerator could be counties in that state that meet a certain criteria, like are rural, or have mineral resources, or voted for a certain party in the last election. The Denominator could represent all counties in that state.

(I realize States have way more counties than this… this is just fake data for this.)

Let’s say we wanted to have a table not with Numerators and Denominators but with Percentages for each state.

We just need to use a DIVIDE function to figure it out.

The DIVIDE function has 3 parts to it.

  1. The 1st part is our numerator
  2. The 2nd part is our denominator
  3. The 3rd part is our alternate result, or what we want this measure to have in it instead of text that says (Blank) if our denominator is 0 (like in Oregon). This part is optional. You don’t need it for the function to work, but it can come in handy.

First, let’s try the DAX without the alternate result:

PERCENTAGE = 
DIVIDE(sum(Data[Numerator]), SUM(Data[Denominator]))

Now we can bring our measure to our table and it’ll figure out the percentages for each State.

You can see that we’re getting a blank space for Oregon which has a denominator of 0, so it’s already working fine for this table.

Where there can sometimes be an issue is when we use this measure somewhere else, like in a Data Card.

Let’s add a little Data Card to our report and bring our measure to it.

It’s showing that overall total percentage of all our States, and if we click on most of our States in the table, that Data Card percentage will change to the % of that State.

However, when we click on Oregon, we get a (Blank) in our Card.

You probably don’t want random (Blank)s showing up on your report, so we can use the Alternate Result part of the DIVIDE function, the 3rd part we haven’t used yet, to either specify a number that we want showing up if the Denominator is zero (like, maybe we want a 0 showing up) OR we can specify that we want a true blank there.

This is how to do it… you just add two double quotation marks (with nothing between them) as your Alternate Result, like this:

PERCENTAGE = 
DIVIDE(sum(Data[Numerator]), SUM(Data[Denominator]), "")

Which gives us an actual blank in that Data Card.

Or maybe you want it (and the spot in your table) to say “NA” when the denominator is zero?

PERCENTAGE = 
DIVIDE(sum(Data[Numerator]), SUM(Data[Denominator]), "NA")

Pretty great, right?

Let’s finish off this post by first making sure Power BI knows that our Percentage measure should be shown as a Percentage.

Just click the Percentage measure over in the Data Pane on the right of the screen and the ribbon at the top will change to a “Measure Tools” ribbon. ON there is a Formatting section with a % button we can click.

Now you can do lots of fun things with the percentage measure, like make a Bar Chart of the percentage of things/items/people/counties/whatever in each state.

A simple yet very useful piece of DAX.

Take care everyone,

Joe.

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.

0 Comments

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>