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.
 

TimRodman

Board Regular
Joined
Jan 21, 2015
Messages
94
It works for me in Excel 2013, both for Calculated Columns and Calculated Fields (Measures).





 

eliwaite

New Member
Joined
Feb 24, 2015
Messages
30
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

Board Regular
Joined
Jan 21, 2015
Messages
94
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

New Member
Joined
Feb 24, 2015
Messages
30
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

Board Regular
Joined
Jan 21, 2015
Messages
94
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?
 

eliwaite

New Member
Joined
Feb 24, 2015
Messages
30
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

Board Regular
Joined
Jan 21, 2015
Messages
94
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.
 

eliwaite

New Member
Joined
Feb 24, 2015
Messages
30
Thanks for explaining what is happening and walking through this issue with me. I guess my only solution is the IF function.
 

Forum statistics

Threads
1,081,845
Messages
5,361,665
Members
400,643
Latest member
RockStar89

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top