Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Complicated Calculation (Please Help)

Hi Everyone,

 

Please help me on this - is this possible to be done? 

 

Scenario 1: 

when the business transformation filter to single activity (activity 1) : 

 

impact on workload: -60%

impact year starts from: 2022 and onwards: 

 

2020: no impact
2021: no impact
2022 - 2030: base * 0.4 

 

Scenario 2:

when the business transformation filter to mulitple activities (activity 1 and 2) : 

 

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Based on your description, you can create a measure as follows.

    Test_measure =
VAR x0 =
    SELECTEDVALUE ( ACT[Activity] )
VAR x1 =
    SWITCH (
        TRUE (),
        MAX ( 'Test_activity'[Year] ) <= 2021, MAX ( 'Test_activity'[units] ),
        MAX ( 'Test_activity'[units] ) * 0.4
    )
VAR x2 =
    SWITCH (
        TRUE (),
        MAX ( 'Test_activity'[Year] ) <= 2021, MAX ( 'Test_activity'[units] ),
        MAX ( 'Test_activity'[Year] ) >= 2025,
            MAX ( 'Test_activity'[units] ) * 0.4 * 0.8,
        MAX ( 'Test_activity'[units] ) * 0.4
    )
RETURN
    IF (
        ISFILTERED ( ACT[Activity] ),
        IF (
            x0 = "activity 1",
            x1,
            IF (
                x0 = "activity 2",
                BLANK (),
                IF (
                    COUNTROWS ( ALLSELECTED ( ACT[Activity] ) ) = DISTINCTCOUNT ( ACT[Activity] ),
                    x2,
                    BLANK ()
                )
            )
        ),
        BLANK ()
    )

    Result:

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Based on your description, you can create a measure as follows.

    Test_measure =
VAR x0 =
    SELECTEDVALUE ( ACT[Activity] )
VAR x1 =
    SWITCH (
        TRUE (),
        MAX ( 'Test_activity'[Year] ) <= 2021, MAX ( 'Test_activity'[units] ),
        MAX ( 'Test_activity'[units] ) * 0.4
    )
VAR x2 =
    SWITCH (
        TRUE (),
        MAX ( 'Test_activity'[Year] ) <= 2021, MAX ( 'Test_activity'[units] ),
        MAX ( 'Test_activity'[Year] ) >= 2025,
            MAX ( 'Test_activity'[units] ) * 0.4 * 0.8,
        MAX ( 'Test_activity'[units] ) * 0.4
    )
RETURN
    IF (
        ISFILTERED ( ACT[Activity] ),
        IF (
            x0 = "activity 1",
            x1,
            IF (
                x0 = "activity 2",
                BLANK (),
                IF (
                    COUNTROWS ( ALLSELECTED ( ACT[Activity] ) ) = DISTINCTCOUNT ( ACT[Activity] ),
                    x2,
                    BLANK ()
                )
            )
        ),
        BLANK ()
    )

    Result:

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,based on what I got

     

    Layer one seem seem like

    new column =

    if([Year] >=2022, [Base]*.4 , [Base])

     

    layer seems like a new column

    Switch( true() ,

    [Year] >=2025, [Base]*.6

    [Year] >=2022, [Base]*.4 ,

    [Base]

    )