Forum Discussion

hackfifi's avatar
hackfifi
Helper V
3 years ago
Solved

Drawdown Curve

Good Day
I am trying to calculate the "Drawdown" row as per the below data table example.
As you can see the TOTAL CUMATIVE Formula used gives the Cumulative by Year resulting in a value of $186
 
I am trying to Acheive the "DrawDown" calculation based on WIN(+) and NO WIN(-) by year
Hence starting from $186 (Total Budget) - "NO WIN" + "WIN" 

So in the below example:
=186-[5+0]-[25+0]-[15+0]-[5+16]-[15+10]-[30+0]-[25+15]-[15+10]
 
Hence, i need support to develop the above measure Cumulative Drawdown by Year
 
Thanks!
 
Total_Cumulative_Overall =
CALCULATE(
    [Budget],
    FILTER(
        ALLSELECTED('_Calendar'),
        ISONORAFTER(_Calendar[Date], MAX(_Calendar[Date]),DESC)
        )
)

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi hackfifi ,

     

    My steps are as follows:

    1. Make a copy in the PowerQuery Eidtor and name it Table1:

    2.Replace Values for column 2017-2026:

    3.Unpivot column for column 2017-2026:

    4.Enter data-> Table2:

    5.New measures:

    Burget = SUM('Table1'[Value])
    Total_Cumulative_Overall = 
CALCULATE (
    [Burget],
    FILTER ( ALL ( 'Table1' ), 'Table1'[Year] <= MAX ( 'Table1'[Year] ) )
)
    win = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALL( 'Table1' ),
        'Table1'[WIN STATUS] = "WIN"
            && 'Table1'[Year] <= MAX ( 'Table1'[Year] )
    )
)
    no win = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALL( 'Table1' ),
        'Table1'[WIN STATUS] = "NO WIN"
            && 'Table1'[Year] <= MAX ( 'Table1'[Year] )
    )
)
    Drawdown = 
VAR _all = CALCULATE([Total_Cumulative_Overall],ALL())
VAR _result = _all - [no win] + [win]
RETURN
_result
    Measure = 
SWITCH(
    SELECTEDVALUE('Table2'[Colunm]),
    "Total_Cumulative_Overall",[Total_Cumulative_Overall],
    "Drawdown",[Drawdown]
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi hackfifi ,

     

    My steps are as follows:

    1. Make a copy in the PowerQuery Eidtor and name it Table1:

    2.Replace Values for column 2017-2026:

    3.Unpivot column for column 2017-2026:

    4.Enter data-> Table2:

    5.New measures:

    Burget = SUM('Table1'[Value])
    Total_Cumulative_Overall = 
CALCULATE (
    [Burget],
    FILTER ( ALL ( 'Table1' ), 'Table1'[Year] <= MAX ( 'Table1'[Year] ) )
)
    win = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALL( 'Table1' ),
        'Table1'[WIN STATUS] = "WIN"
            && 'Table1'[Year] <= MAX ( 'Table1'[Year] )
    )
)
    no win = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALL( 'Table1' ),
        'Table1'[WIN STATUS] = "NO WIN"
            && 'Table1'[Year] <= MAX ( 'Table1'[Year] )
    )
)
    Drawdown = 
VAR _all = CALCULATE([Total_Cumulative_Overall],ALL())
VAR _result = _all - [no win] + [win]
RETURN
_result
    Measure = 
SWITCH(
    SELECTEDVALUE('Table2'[Colunm]),
    "Total_Cumulative_Overall",[Total_Cumulative_Overall],
    "Drawdown",[Drawdown]
)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data