Forum Discussion

Huiyan's avatar
Huiyan
Regular Visitor
5 years ago
Solved

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? ...
Help with DAX
  • Fowmy's avatar
    Fowmy
    5 years ago

    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 )
Fowmy's avatar
Fowmy
Super User
5 years ago

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?

Huiyan's avatar
Huiyan
Regular Visitor
5 years ago

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 

 

 

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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 )