Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |