# Make DIVIDE Return a Zero when Numerator is Zero in PowerPivot

#### eliwaite

##### New Member
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
It works for me in Excel 2013, both for Calculated Columns and Calculated Fields (Measures).

#### eliwaite

##### New Member
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
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
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
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
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
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
Thanks for explaining what is happening and walking through this issue with me. I guess my only solution is the IF function.

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

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