topic Define Percentage format in DAX in DAX Commands and Tips

Define Percentage format in DAX

carlol — Thu, 16 Jan 2020 18:05:21 GMT

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

Re: Define Percentage format in DAX

dm-p — Thu, 16 Jan 2020 22:32:26 GMT

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 🙂

Re: Define Percentage format in DAX

carlol — Fri, 17 Jan 2020 12:59:51 GMT

Thanks Daniel!