Forum Discussion

RodrigoTXRA's avatar
RodrigoTXRA
Icon for Helper I rankHelper I
8 years ago
Solved

Dax formula - dealing with zero amounts

Hi All Hope someone can shed some light on this formula below. Long story short, it works well as long as all the items contains some type of value, as soon as one of the items is ZERO, this formu...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Here it is...

     

    PERFORMANCE WINS =IF( ISBLANK([SUM OF WINS]),0,[SUM OF WINS])/ IF( ISBLANK([SUM OF TARGET WINS]),0,[SUM OF TARGET WINS])  *100

     

    I have replaced the blank by 0, however you can change with your desired value.

     

    Thanks
    Raj

Thejeswar's avatar
Thejeswar
Icon for Super User rankSuper User
8 years ago

RodrigoTXRA,

Your DAX is fine.

 

But are you sure, this is happening when one of the values is 0.

I tried the same and find below the result I got

 

Also "Fails to Calculate means" you mean it is returning NULL Values or is it giving an error?

 

If it is giving NULL Value, then your items should be having NULL as their value and not Zero.

 

Regards,

Thejeswar

 

 

  • RodrigoTXRA's avatar
    RodrigoTXRA
    Icon for Helper I rankHelper I
    8 years ago

    Thejeswar Thanks so much for the effort in analysing this query.

    I believe the issue is when the data is NULL - see below and appreciate your thoughts on this

     

    • Thejeswar's avatar
      Thejeswar
      Icon for Super User rankSuper User
      8 years ago

      RodrigoTXRA,

      Any tool when it has NULLs in the calculation, returns NULL. Because NULLs cannot be  equated to any value automatically

       

      So in this case replace NULLs with Zero wherever needed.

      That should solve the issue!!!

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Exactly. Your formula is straight forward and no issues with that. Any issues should be with your data and it may have NULL . Try using Isblank (value,0,value) if you have blank. Similarly handle NULL as well.

     

    Thanks

    Raj