Forum Discussion

Diptarup's avatar
Diptarup
Icon for Helper II rankHelper II
5 years ago
Solved

To get top 5 lowest value based on recent activity

I wanted to get the top 5 lowest value based on the most recent activity perform. Lets take an example, in the below table, I have activity starting from A to J. Based on the recent activity Top 5 Lo...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Diptarup 

    For your requirement, you can try to ceate a calculated table as below:

    Table 2 =
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Activity],
        "Year", CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Activity] ) ),
        "Value",
            CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Year]
                        = CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Activity] ) )
                )
            )
    )
VAR tab2 =
    ADDCOLUMNS ( tab, "Rank", RANKX ( tab, [Value],, ASC, SKIP ) )
RETURN
    FILTER ( tab2, [Rank] <= 5 )
//return TOPN(5,tab,[Value],DESC)

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi， Diptarup 

The information you have provided is not making the problem clear to me.

What is your sorting priority for these three columns?
(value>Year>activity  or  activity>year>value ........)

 

Best Regards,
Community Support Team _ Eason

Diptarup's avatar
Diptarup
Icon for Helper II rankHelper II
5 years ago

I wanted to have the bottom 5 Activities value based on the most recent assessment done. So , if any of the Activity which was performed at a later year needs to be omitted while ranking the lowest value. 

 

So For A, I will chose A-10 as the value for ranking should be based on 2020 result

For B, I will choose B- 11, as the value for ranking should be based on 2019 result (latest year for B)

 

Basically what I am trying to do is to find the value of the most recent activity and then rank them againts each other and then determining the lowest 5 values from them.

Result expected:-

Year Activity Value
1/1/2018 F 6
1/1/2020 E 8
1/1/2020 A 10
1/1/2017 D 10
1/1/2019 B 11

 

Hope this answer your query.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Diptarup 

    For your requirement, you can try to ceate a calculated table as below:

    Table 2 =
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Activity],
        "Year", CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Activity] ) ),
        "Value",
            CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Year]
                        = CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Activity] ) )
                )
            )
    )
VAR tab2 =
    ADDCOLUMNS ( tab, "Rank", RANKX ( tab, [Value],, ASC, SKIP ) )
RETURN
    FILTER ( tab2, [Rank] <= 5 )
//return TOPN(5,tab,[Value],DESC)

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.