Receiving result NaN from DAX measure

[LYBridges]

It looks like it's due to using "/" vs DIVIDE, but I'm not sure how to fix the DAX to allow for 0's in the calculations. Any suggestions?

OverallReady =
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Group Structure Config"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Group Structure Config"), 'Append1'[TotalVolExpected])
) +
(
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Provider Demographics"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Provider Demographics"), 'Append1'[TotalVolExpected])
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Network"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Network"), 'Append1'[TotalVolExpected])
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Prior Authorization"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Prior Authorization"), 'Append1'[TotalVolExpected])
)
) / 3
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "New Membership"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "New Membership"), 'Append1'[TotalVolExpected])
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Product Builds"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Product Builds"), 'Append1'[TotalVolExpected])
) +
AVERAGEX(
FILTER('Inputs - Claims Ready', 'Inputs - Claims Ready'[Layer] = 3),
'Inputs - Claims Ready'[Overall % Complete]
)
) / 5

[Poojara_D12 · Accepted solution]

Hi @LYBridges 

To handle division by zero issues in your DAX calculation, you can replace the / operator with the DIVIDE function. The DIVIDE function has an optional third parameter that allows you to specify an alternative result if the denominator is zero. This prevents errors when TotalVolExpected is zero.

Here's how to rewrite your OverallReady measure using DIVIDE instead of /:

OverallReady =
(
    SUMX(
        FILTER('Append1', 'Append1'[Category] = "Group Structure Config"),
        DIVIDE(
            'Append1'[TotalVolConfigured],
            SUMX(FILTER('Append1', 'Append1'[Category] = "Group Structure Config"), 'Append1'[TotalVolExpected]),
            0
        )
    ) +
    (
        DIVIDE(
            SUMX(
                FILTER('Append1', 'Append1'[Category] = "Provider Demographics"),
                'Append1'[TotalVolConfigured]
            ),
            SUMX(FILTER('Append1', 'Append1'[Category] = "Provider Demographics"), 'Append1'[TotalVolExpected]),
            0
        ) +
        DIVIDE(
            SUMX(
                FILTER('Append1', 'Append1'[Category] = "Network"),
                'Append1'[TotalVolConfigured]
            ),
            SUMX(FILTER('Append1', 'Append1'[Category] = "Network"), 'Append1'[TotalVolExpected]),
            0
        ) +
        DIVIDE(
            SUMX(
                FILTER('Append1', 'Append1'[Category] = "Prior Authorization"),
                'Append1'[TotalVolConfigured]
            ),
            SUMX(FILTER('Append1', 'Append1'[Category] = "Prior Authorization"), 'Append1'[TotalVolExpected]),
            0
        )
    ) / 3 +
    DIVIDE(
        SUMX(
            FILTER('Append1', 'Append1'[Category] = "New Membership"),
            'Append1'[TotalVolConfigured]
        ),
        SUMX(FILTER('Append1', 'Append1'[Category] = "New Membership"), 'Append1'[TotalVolExpected]),
        0
    ) +
    DIVIDE(
        SUMX(
            FILTER('Append1', 'Append1'[Category] = "Product Builds"),
            'Append1'[TotalVolConfigured]
        ),
        SUMX(FILTER('Append1', 'Append1'[Category] = "Product Builds"), 'Append1'[TotalVolExpected]),
        0
    ) +
    AVERAGEX(
        FILTER('Inputs - Claims Ready', 'Inputs - Claims Ready'[Layer] = 3),
        'Inputs - Claims Ready'[Overall % Complete]
    )
) / 5

Explanation

• Each division operation now uses DIVIDE(numerator, denominator, 0), which will return 0 if the denominator is 0, avoiding any divide-by-zero errors.
• The optional third parameter in DIVIDE (here set to 0) specifies what should be returned when the denominator is 0. You can adjust this to another default value if desired.

This approach should solve the problem and prevent errors due to zero values in the denominator. Let me know if this works or if you need further adjustments!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[LYBridges]

Thanks so much! That solved the error!

[bhanu_gautam]

@LYBridges , Try using 

OverallReady =
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Group Structure Config"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Group Structure Config"), 'Append1'[TotalVolExpected]), 0)
) +
(
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Provider Demographics"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Provider Demographics"), 'Append1'[TotalVolExpected]), 0)
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Network"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Network"), 'Append1'[TotalVolExpected]), 0)
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Prior Authorization"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Prior Authorization"), 'Append1'[TotalVolExpected]), 0)
)
) / 3
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "New Membership"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "New Membership"), 'Append1'[TotalVolExpected]), 0)
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Product Builds"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Product Builds"), 'Append1'[TotalVolExpected]), 0)
) +
AVERAGEX(
FILTER('Inputs - Claims Ready', 'Inputs - Claims Ready'[Layer] = 3),
'Inputs - Claims Ready'[Overall % Complete]
)
) / 5