Make DIVIDE Return a Zero when Numerator is Zero in PowerPivot

eliwaite

New Member
Joined
Feb 24, 2015
Messages
30
I am running into a problem with the DIVIDE function in PowerPivot. When the numerator is Zero, it returns a result of blank. Example: =DIVIDE(0,1)="" in PowerPivot. I have also tried adding an alternative result, such as =DIVIDE(0,1,0)="" which still returns a blank. Now when I just write the formula in Excel, I get the expected result of Zero, for example =0/1=0.

I know I can go into the Pivot Table options and change "For empty cells show: 0", however that makes all blanks in the Pivot Table zeros, and there are other place that I actually want blanks.

The best solution I have come up with is to use an IF function, =IF([Numerator]=0,0,DIVIDE(NUMERATOR,DENOMINATOR)) However this solution seems cumbersome (especially when you add all the filters I am using to determine the numerator) and I think there should be a better way of handling this.

If you know of a trick to get PowerPivot to return a Zero when the Numerator is Zero I would appreciate it.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
It works for me in Excel 2013, both for Calculated Columns and Calculated Fields (Measures).

KAe8E89.png


DvILBR7.png


t0J8aMp.png
 
Upvote 0
Thanks for checking. Maybe I have a different error in my formula that is causing the problem. Here is the actual formula that I am using: =DIVIDE(CALCULATE(DISTINCTCOUNT('C1 Roster'[EAM Name]),FILTER(Opps,[Opportunity Status]="Won"&&[Business Area Code]="3S00"&&[Opportunity Close Fiscal Year Quarter Code]="2015Q2")),[# of EAMs]) Do you see any issues with it?
 
Upvote 0
Have you tried dropping [# of EAMs] next to your DIVIDE calculated field on your pivot table to make sure that it isn't empty or 0?
 
Upvote 0
Just tried. I dropped the # of EAMs and put in a 1, as such =DIVIDE(CALCULATE(DISTINCTCOUNT('C1 Roster'[EAM Name]),FILTER(Opps,[Opportunity Status]="Won"&&[Business Area Code]="3S00"&&[Opportunity Close Fiscal Year Quarter Code]="2015Q2")),1) I am getting the same results. For people who don't have a Q2 Won Deal for 3S00, it is blank, for everyone else it works fine.
 
Upvote 0
So, when you use this formula EVERYTHING is blank?
Code:
[COLOR=#333333]=DIVIDE(CALCULATE(DISTINCTCOUNT('C1 Roster'[EAM Name]),FILTER(Opps,[Opportunity Status]="Won"&&[Business Area Code]="3S00"&&[Opportunity Close Fiscal Year Quarter Code]="2015Q2")),[# of EAMs])[/COLOR]

But when you use this formula only those without Q2 Won Deal for 3S00 are blank?
Code:
[COLOR=#333333]=DIVIDE(CALCULATE(DISTINCTCOUNT('C1 Roster'[EAM Name]),FILTER(Opps,[Opportunity Status]="Won"&&[Business Area Code]="3S00"&&[Opportunity Close Fiscal Year Quarter Code]="2015Q2")),1)[/COLOR]


Then the problem must be that
[# of EAMs] is blank right? But when you put [# of EAMs] on your pivot table it isn't blank?
 
Upvote 0
Sorry for the confusion. Both formulas return the same results. When the denominator is [# of EAms] or 1, the people with won deals shows up properly, but the people without deals show up as blanks.
 
Upvote 0
Ah, I think you're getting this because you have the following in your filter clause:
Code:
[COLOR=#333333][Opportunity Status]="Won"[/COLOR]

Which causes the CALCULATE function to operate on an empty table, thus returning a blank value.

You can do an ISBLANK test to return 0 if it's blank like this:
Code:
=IF(ISBLANK(DIVIDE(CALCULATE(DISTINCTCOUNT('C1 Roster'[EAM Name]),FILTER(Opps,[Opportunity Status]="Won"&&[Business Area Code]="3S00"&&[Opportunity Close Fiscal Year Quarter Code]="2015Q2")),[# of EAMs])),0,DIVIDE(CALCULATE(DISTINCTCOUNT('C1 Roster'[EAM Name]),FILTER(Opps,[Opportunity Status]="Won"&&[Business Area Code]="3S00"&&[Opportunity Close Fiscal Year Quarter Code]="2015Q2")),[# of EAMs]))

It looks ugly, but I think it works. What we really need is an IFBLANK function which would make the above a lot more readable.
 
Upvote 0
Thanks for explaining what is happening and walking through this issue with me. I guess my only solution is the IF function.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top