Forum Discussion

Mp1977's avatar
Mp1977
Icon for Helper II rankHelper II
6 years ago
Solved

Transform multiple measures in a single one inside a Virtual Table

Hi !

I have a table that shows me trading results over a period of time. I want to calculate the max loss I had.

Here you can download the file, if needed.  https://1drv.ms/u/s!AvNOngq_bKmCkoJQdXvzJSnPvMwT8w?e=KqN4SC

First, I need to calculate the cumulative total of the result, which I use this measure: 

Cumulative Result = CALCULATE( [Result];

FILTER(
ALL('Track Recording'[Date]) ;
'Track Recording'[Date]<= MAX('Track Recording'[Date] )
)
)


Then I need to calculate the Cumulative Max and I used this measure:

Cumulative Max Result = CALCULATE( MAXx(
FILTER (
ALLSELECTED ( 'Track Recording'[Date] );
'Track Recording'[Date] <= MAX ( 'Track Recording'[Date] )
); [Cumulative Result])
)

Finally, I subtract the  the Cumulative Max from Cumulative total with this measure.
Max Loss = [Cumulative Max Result]-[Cumulative Result]

The last step would return the max value from Max Loss above. I could do a little bit by creating a Table in the Pbi visualization, but what I want to do is transform all measures in a single one. I know that i need to create a virtual table, but I was unable so far to create a virtual calculated column refering to other virtual calculating column,

The value that I am looking for is 2.184. As you can see in the picture bellow.

 

 




all help is really appreciated.

M.P.
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hello @Mp1977 ,

    Try this:

    Measure =
VAR tab =
    ADDCOLUMNS (
        'Track Recording',
        "Cumulative_Result", CALCULATE (
            SUM ( 'Track Recording'[Result] ),
            FILTER (
                ALL ( 'Track Recording' ),
                'Track Recording'[Date] <= EARLIER ( 'Track Recording'[Date] )
            )
        )
    )
VAR tab2 =
    ADDCOLUMNS (
        ALL ( 'Track Recording' ),
        "Cumulative_Result", CALCULATE (
            SUM ( 'Track Recording'[Result] ),
            FILTER (
                ALL ( 'Track Recording' ),
                'Track Recording'[Date] <= EARLIER ( 'Track Recording'[Date] )
            )
        )
    )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Cumulative_Result_Max",
        VAR _date = [Date]
        RETURN
            MAXX ( FILTER ( tab2, [Date] <= _date ), [Cumulative_Result] )
    )
VAR newtab2 =
    ADDCOLUMNS ( newtab, "Max_Loss", [Cumulative_Result_Max] - [Cumulative_Result] )
RETURN
    MAXX ( newtab2, [Max_Loss] )

    cum.PNG

    Best regards

    Icey

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

2 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hello @Mp1977 ,

    Try this:

    Measure =
VAR tab =
    ADDCOLUMNS (
        'Track Recording',
        "Cumulative_Result", CALCULATE (
            SUM ( 'Track Recording'[Result] ),
            FILTER (
                ALL ( 'Track Recording' ),
                'Track Recording'[Date] <= EARLIER ( 'Track Recording'[Date] )
            )
        )
    )
VAR tab2 =
    ADDCOLUMNS (
        ALL ( 'Track Recording' ),
        "Cumulative_Result", CALCULATE (
            SUM ( 'Track Recording'[Result] ),
            FILTER (
                ALL ( 'Track Recording' ),
                'Track Recording'[Date] <= EARLIER ( 'Track Recording'[Date] )
            )
        )
    )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Cumulative_Result_Max",
        VAR _date = [Date]
        RETURN
            MAXX ( FILTER ( tab2, [Date] <= _date ), [Cumulative_Result] )
    )
VAR newtab2 =
    ADDCOLUMNS ( newtab, "Max_Loss", [Cumulative_Result_Max] - [Cumulative_Result] )
RETURN
    MAXX ( newtab2, [Max_Loss] )

    cum.PNG

    Best regards

    Icey

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