Forum Discussion

dave_f's avatar
dave_f
New Member
3 years ago
Solved

Show 0% if blank

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?
Need Help
  • Jaywant's avatar
    Jaywant
    3 years ago

    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.

     

3 Replies

  • Jaywant's avatar
    Jaywant
    Regular Visitor
    3 years ago

    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.

     

    • dave_f's avatar
      dave_f
      New Member
      3 years ago

      Worked like a charm!  Thank you very much 🙂

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    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