Define Percentage format in DAX

Hi carlol,

If I understand your challenge, you want something like the corresponding value on the right for the calculated value on the left?

If so, your original measure is almost there - the issue is that the 100 you're returning for your > 100 scenario is being treated as a raw number, and then a text value for anything less than that in the form of the FORMAT calculation. The result is just stuck in as resolved.

To make all values the same type, just modify the 100 to the text value "100%", e.g.:

Coverage% =
IF (
    DIVIDE ( SUM ( 'IHFD vwCoverage'[IHFD] ), SUM ( 'IHFD vwCoverage'[HIPE] ) ) >= 1,
    "100%",
    FORMAT (
        DIVIDE ( SUM ( 'IHFD vwCoverage'[IHFD] ), SUM ( 'IHFD vwCoverage'[HIPE] ) ),
        "0.0%"
    )
)

(note that your orignal formula would return anything derived as 100% as "100.0%" as this isn't greater than 1, so I modified the operator to be greater than or equal to)

Here's an alternative solution that reduces calculation logic for the % result and gives you a threshold you can easily modify if you change your logic later on:

Coverage % =
VAR Threshold = 1
VAR Result =
    DIVIDE ( SUM ( 'IHFD vwCoverage'[IHFD] ), SUM ( 'IHFD vwCoverage'[HIPE] ) )
RETURN
    IF (
        Result >= Threshold,
        FORMAT ( Threshold, "0%" ),
        FORMAT ( Result, "0.0%" )
    )

Hopefully this is all you need to carry on.

Good luck!

Daniel

If my post helps, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂