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.
Hey Guys,
Can you kindly guide me on the formula below? If formula result is bigger than 1.5, i want to cap it at 1.5, otherwise I need to keep the calculated result. Is there a way to do this?
Thanks in advance!
Huiyan
Solved! Go to Solution.
@Huiyan
Can you try this formula?
Achievement_viz =
VAR one =
CALCULATE (
IF (
[Culmulative] = 0,
SUM ( Data[A2020] ) / SUM ( Data[M2020] ),
(
( SUM ( Data[A2020] ) - SUM ( Data[Baseline] ) )
/ ( SUM ( Data[M2020] ) - SUM ( Data[Baseline] ) )
)
)
)
RETURN
IF ( one >= 1.5, 1.5, one )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Huiyan
Can you paste the formula in this replay box as text to check?.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Millions thanks!
@Huiyan
Can you explain where you are getting the wrong results, you can share the screenshot that includes all the fields. Are you creating a Measure or a Calculated column?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I'm creating a measure. You can see in the table below, there are only '150%' because I did the cap. For the empty cells, I want them to be filled with results of the underlined formula, e.g. 140%, 130%,80% etc. I need to revise the "" part. But I don't know how. @Fowmy
@Huiyan
Can you try this formula?
Achievement_viz =
VAR one =
CALCULATE (
IF (
[Culmulative] = 0,
SUM ( Data[A2020] ) / SUM ( Data[M2020] ),
(
( SUM ( Data[A2020] ) - SUM ( Data[Baseline] ) )
/ ( SUM ( Data[M2020] ) - SUM ( Data[Baseline] ) )
)
)
)
RETURN
IF ( one >= 1.5, 1.5, one )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |