Division by Zero fix changing visuals

[Anonymous]

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?

[v-eqin-msft · Accepted solution]

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

[ryan_mayu]

@Anonymous 

have you tried DIVIDE function?

https://docs.microsoft.com/en-us/dax/divide-function-dax

[negi007]

@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]))