Forum Discussion

carlol's avatar
carlol
Icon for Resolver I rankResolver I
6 years ago
Solved

Define Percentage format in DAX

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%"))
 
For Percentages greater than 100% I set to 100% , but I want to display 100% (No decimal place 0%) in this instance and when percentage is less than 100 .ie. 98.7 I want to illustrate as 0.0%
 
I tried setting the measure to General format via the modelling menu and I tried setting the default format to Percentage with 0 decimal places in the via the modelling menu, use dax as above to set to 1 decemial place 
 
Can this be done in dax, have two different formats based on derived value
 
 
 
Help required
  • dm-p's avatar
    dm-p
    6 years ago

    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 🙂

2 Replies

  • dm-p's avatar
    dm-p
    Icon for Super User rankSuper User
    6 years ago

    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 🙂