Need help error handling zero divided by zero

[marknelson860]

Hello All, 

So i have a pretty extensive dashboard and looking for a bit of help. Lets say we have a one column that is account balance and another column that is account credit limit. We then have a third calculated column that is formulated with dax using the proper syntax for dividing balance by credit limit. If an account expires its credit limit will go to 0. the account will still have a balance or it will be 0 but both will return errors. Just replacing all the errors with 0 is not an option either because that will reduce the utilization to -0- even if that is not true. 

The ideal solution would be to do balance / credit limit IF credit limit is greater than 0 if not go back and use the next most recent value greater than 0. each account has 12 months of data and its all lumped into each of the respective categories. Usually they will only go to 0 for maybe a month or two. 

Any help would be appreciated. thank you. 

[v-yanjiang-msft]

Hi @marknelson860 ,

According to your description, I create a sample to clarify:

Here's my solution, create a calculated column:

Column =
VAR _pre =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] < EARLIER ( 'Table'[Month] )
                && 'Table'[Credit limit] <> 0
        ),
        'Table'[Month]
    )
RETURN
    IF (
        [Credit limit] <> 0,
        DIVIDE ( [Balance], [Credit limit] ),
        DIVIDE (
            [Balance],
            MAXX ( FILTER ( 'Table', 'Table'[Month] = _pre ), 'Table'[Credit limit] )
        )
    )

The logic is to refer to the recent previous Credit limit which isn't 0 when the current Credit limit is 0. Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marknelson860]

Hi - Thank you for the response. 

This is working for me below after i changed my tables and names and stuff. The only other thing that I really need to finish in order to double check it is what the non zero credit limits are that are not the utilization divide calculation - just the credit limits. I have been trying to modify this code you graciously provided but cant get it. Further assistance would be greatly appreciated. 

NoZeroCreditUtilization =

VAR _pre =

    MAXX (

        FILTER (

            ALL ( 'balances' ),

            'balances'[Month] < EARLIER ( 'balances'[Month] )

                && 'balances'[CreditLimit] <> 0

        ),

        'balances'[Month]

    )

RETURN

    IF (

        'balances'[CreditLimit] <> 0,

        DIVIDE ( 'balances'[Value], 'balances'[CreditLimit] ),

        DIVIDE (

            'balances'[Value],

            MAXX ( FILTER ( 'balances', 'balances'[Month] = _pre ), 'balances'[CreditLimit] )

        )

    )

[Idrissshatila]

Hello @marknelson860 ,

Did you try to use the Divide function and out the alternative to 0, I think it should work.

use this measure = DIVIDE(<numerator>, <denominator> , 0)

this 0 at the last will solve your problem.

if you want to read more about the Divide function refer to this https://learn.microsoft.com/en-us/dax/divide-function-dax

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea 💡