Show 0% if blank

[dave_f]

I wrote the following DAX to calculate a percentage, but I want it to show "0%" if blank and I can't get it to work 😞

LOBs Penetrated percent equal to 1 = VAR __FILTERED_VALUE = CALCULATE(

    COUNT(

        'R&B LOB Revenue'[LOBs Penetrated]

    ),

    KEEPFILTERS(

        'R&B LOB Revenue'[LOBs Penetrated]

        = 1

    )

)

VAR __MEASURE_VALUE = COUNT(

    'R&B LOB Revenue'[LOBs Penetrated]

)

RETURN

    DIVIDE(

        __FILTERED_VALUE,

        __MEASURE_VALUE

    )

I don't know where to put the 'if(isblank' statement.  Can anyone please help?

[Jaywant · Accepted solution]

If you want the result to be displayed as "0%" when it is blank, you can modify the DAX formula as follows:

LOBs Penetrated percent equal to 1 =
VAR __FILTERED_VALUE =
CALCULATE(
COUNT('R&B LOB Revenue'[LOBs Penetrated]),
KEEPFILTERS('R&B LOB Revenue'[LOBs Penetrated] = 1)
)
VAR __MEASURE_VALUE = COUNT('R&B LOB Revenue'[LOBs Penetrated])
VAR __RESULT =
DIVIDE(
__FILTERED_VALUE,
__MEASURE_VALUE
)
RETURN
IF(
ISBLANK(__RESULT),
"0%",
FORMAT(__RESULT, "0%")
)

In this modified formula, the IF function checks if the result (__RESULT) is blank using the ISBLANK function. If it is blank, the formula returns the text value "0%". Otherwise, it formats the result as a percentage using the FORMAT function.

Now, when the result is blank, it will be displayed as "0%", and when it is not blank, it will be displayed as a percentage value.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Connect with me on LinkedIn

[dave_f]

Worked like a charm!  Thank you very much 🙂

[Arul]

@dave_f ,

Have you tried this one? By keeping output in the result variable and use conditional statement in return,

LOBs Penetrated percent equal to 1 = VAR __FILTERED_VALUE = CALCULATE(
    COUNT(
        'R&B LOB Revenue'[LOBs Penetrated]
    ),
    KEEPFILTERS(
        'R&B LOB Revenue'[LOBs Penetrated]
        = 1
    )
)
VAR __MEASURE_VALUE = COUNT(
    'R&B LOB Revenue'[LOBs Penetrated]
)
VAR _result =  DIVIDE(
        __FILTERED_VALUE,
        __MEASURE_VALUE
    )

RETURN IF(ISBLANK(_resutl),"0%",_result)

Thanks,

Arul