cancel
Showing results for
Did you mean:
Regular Visitor

## Multiple IF and return calculated results if false

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?

Huiyan

1 ACCEPTED SOLUTION
Super User IV

@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 )
``````
Did I answer your question? Mark my post as a solution! and hit thumbs up

5 REPLIES 5
Super User IV

@Huiyan

Can you paste the formula in this replay box as text to check?.

Did I answer your question? Mark my post as a solution! and hit thumbs up

Regular Visitor

@Fowmy  Millions thanks!

Achievement_viz = CALCULATE(IF((IF([Culmulative]="0",SUM(Data[A2020])/sum(Data[M2020]),((SUM(Data[A2020])-sum(Data[Baseline]))/(SUM(Data[M2020])-SUM(Data[Baseline])))))>1.5,1.5,""))
Super User IV

@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?

Did I answer your question? Mark my post as a solution! and hit thumbs up

Regular Visitor

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

Super User IV

@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 )
``````
Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.