locked
Complex DAX Calculation RRS feed

  • Question

  • Hi,

    I have an excel calculation that I have tried to convert i into a powerpivot measure. The excel calcualtion is as below:

     (IFERROR(IF(1-ABS((TOTDEMANDQTY-REVISEDFRC)/TOTDEMANDQTY)<0,0,(1-ABS((TOTDEMANDQTY-REVISEDFRC)/TOTDEMANDQTY))),"0.00")

    I was given some great advice to change it to the below as a measure and it almost works but not quite:

    Some Measure:=if(DIVIDE(ABS(FAReportInput[Demand]-FAReportInput[Revised Forecast]),FAReportInput[Demand],0)<1,1-DIVIDE(ABS(FAReportInput[Demand]-FAReportInput[Revised Forecast]),FAReportInput[Demand],0),0)

    The issue is where I have a revised forecast of an amount but a demand of zero I am returning a value of 1 when in fact it should return a value of zero as per the iferror in the excel formula- can anyone help?

    Thanks

    Tuesday, February 17, 2015 2:47 PM

Answers

  • Hi,

    This just needed a second measure adding so all sorted.

    • Proposed as answer by Michael Amadi Tuesday, February 17, 2015 9:00 PM
    • Marked as answer by Charlie Liao Monday, February 23, 2015 5:29 AM
    Tuesday, February 17, 2015 4:26 PM

All replies

  • Hi,

    This just needed a second measure adding so all sorted.

    • Proposed as answer by Michael Amadi Tuesday, February 17, 2015 9:00 PM
    • Marked as answer by Charlie Liao Monday, February 23, 2015 5:29 AM
    Tuesday, February 17, 2015 4:26 PM
  • Hi Grinch,

    Glad to hear that your issue had been solved by yourself. Thank you for your sharing which will help other forum members who have the similar issue.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, February 23, 2015 5:31 AM