Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Division by Zero fix changing visuals

I have a calculation on two measures, SUM('QGenda Compliance'[CompliantClockIn/Out])/SUM('QGenda Compliance'[RequiredClockIn/Out]) resulting in NaN because the denominator can be 0.  I've been trying to fix this with:

Overall Compliance = IF(SUM('QGenda Compliance'[RequiredClockIn/Out])=0,0,SUM('QGenda Compliance'[CompliantClockIn/Out])/SUM('QGenda Compliance'[RequiredClockIn/Out])) which works to get rid of NaN but it has other strange effects.  I have a report with a matrix and some line graphs filtered by a date range of June - September.  When I change this formula these visuals then show the entire date range, say Jan - Dec, but has a 0 for every month except June - September.  Why does it change it to show the entire date range and not just what the user picks on the slider?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    According to my understand , you want to diaplay the measure result based on Months selected in Slicer , right?

    You could use the following formula:

    Overall Compliance =
IF (
    'QGenda Compliance'[RequiredClockIn/Out] = 0,
    0,
    'QGenda Compliance'[CompliantClockIn/Out] / 'QGenda Compliance'[RequiredClockIn/Out]
)

    Or use DIVIDE()

    Overall Compliance =
DIVIDE (
    'QGenda Compliance'[CompliantClockIn/Out],
    'QGenda Compliance'[RequiredClockIn/Out]
)

    The matrix visual and line chart look like this:

     

    Here is the pbix file.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

3 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous  Can you use Blank() instead of zero. I hope it works for you

     

    IF(SUM('QGenda Compliance'[RequiredClockIn/Out])=0,Blank(),SUM('QGenda Compliance'[CompliantClockIn/Out])/SUM('QGenda Compliance'[RequiredClockIn/Out]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    According to my understand , you want to diaplay the measure result based on Months selected in Slicer , right?

    You could use the following formula:

    Overall Compliance =
IF (
    'QGenda Compliance'[RequiredClockIn/Out] = 0,
    0,
    'QGenda Compliance'[CompliantClockIn/Out] / 'QGenda Compliance'[RequiredClockIn/Out]
)

    Or use DIVIDE()

    Overall Compliance =
DIVIDE (
    'QGenda Compliance'[CompliantClockIn/Out],
    'QGenda Compliance'[RequiredClockIn/Out]
)

    The matrix visual and line chart look like this:

     

    Here is the pbix file.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin