Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Need help error handling zero divided by zero

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 ha...
Need Help
v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi Anonymous ,

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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