Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
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 🙂
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
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 🙂
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
Thanks Daniel!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |