Make DIVIDE Return a Zero when Numerator is Zero in PowerPivot

[eliwaite]

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.

 

[TimRodman]

It works for me in Excel 2013, both for Calculated Columns and Calculated Fields (Measures).

 

[eliwaite]

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?

 

[TimRodman]

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?

 

[eliwaite]

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.

 

[TimRodman]

So, when you use this formula EVERYTHING is blank?

[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?

[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?

 

[eliwaite]

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.

 

[TimRodman]

Ah, I think you're getting this because you have the following in your filter clause:

[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:

=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.

 

[eliwaite]

Thanks for explaining what is happening and walking through this issue with me. I guess my only solution is the IF function.