DAX divide issue

[Cime14]

Hi all,

how could I correct the formula in power pivot measures to get value 0% where numerator and denominator are both 0?
I was able to resolve this issue where only denominator is 0 but I am lost when both are.

My current formula:
=DIVIDE(CALCULATE(SUM(SAP_Final[MTD]));CALCULATE(SUM(FCRS_PM[MTD]));1)-1

Would appreciate your help!

Have a nice day!

 

[Cime14]

Anyone know the solution?

 

[RoryA]

You could wrap it in an IFERROR(your formula, 0) function.

 

[Cime14]

Hi,

@RoryA thanks for your reply but it doesn't solve my problem
Any other idea?

 

[RoryA]

How does it not solve your problem?

 

[Cime14]

With my current formula: =DIVIDE(CALCULATE(SUM(SAP_Final[MTD]));CALCULATE(SUM(FCRS_PM[MTD]));1)-1 I am getting -100%.

 

[RoryA]

Oh, sorry, I completely misinterpreted your formula. Try something like:

=VAR PctChange = DIVIDE(CALCULATE(SUM(SAP_Final[MTD]));CALCULATE(SUM(FCRS_PM[MTD]));1)
RETURN IF(PctChange=0,0,PctChange-1)

 

[Cime14]

Now I am getting value 0% on all lines...except on a line I would want to - there is still -100% ?

 

[RoryA]

Sorry I meant to change the 1 in the DIVIDE function to 0 but I can’t see how that would give you 0 everywhere else as it was

 

[pjmorris]

worth noting that the result of 0/0 is a philosophical challenge: it could be 0, 1 or Infinity. Why not explicitly test for both figures being 0 with an IF function wrapping the division. If I've understood correctly it would read:

=IF(AND(SUM(SAP_Final[MTD])=0, SUM(FCRS_PM[MTD])=0), 0, DIVIDE(SUM(SAP_Final[MTD]), SUM(FCRS_PM[MTD]), 1)-1) but i'm not sure why your subtracting 1 from the result of the divide as that applies to all results returned. Also, I dont think (but happy to be proved wrong) that you need to wrap the SUM calculations in CALCULATE. I think you could define two additional measures as follows:

SumFinal=SUM(SAP_Final[MTD])
SumFCRS_PM=SUM(FCRS_PM[MTD])

Your percentage measure then becomes:

SAP_PM_MTD_%=IF(AND(SumFinal=0, SumFCRS_PM=0), 0, DIVIDE(SumFinal, SumFCRS_PM,0))

PS. you might need to change my commas into semicolons.

HTH