Forum Discussion

vamsi_tiger's avatar
vamsi_tiger
Frequent Visitor
3 years ago
Solved

get nth largest value based on other column

i have a table as below

datereasonduration
23/6breakdown5
24/6snacks10
22/6breakdown2
21/6shutdown50
21/6other1
24/6other2
24/6break5

so if the select the time slicer on the page to 21 to 24 it should filter the values in the table to that time line and aggregate the duration of that perticualar reason and sort is as per the total duration and give me the reason. 
so lets say i need 2nd largest reason with respect to selected duration(eample 21-24 considering total table as above) the measure should give me "snacks" since it has 10 min
power PowerBI PBCommunity 

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi vamsi_tiger 
    Please refer to attached sample file with the proposed solution

    nth Largest Reason = 
MAXX ( 
    TOPN (
        1,
        TOPN ( 
            SELECTEDVALUE ( 'nth Largest'[n] ),
            SUMMARIZE ( 
                'Table',
                'Table'[reason],
                "@Duration", SUM ( 'Table'[duration] )
            ),
            [@Duration]
        ),
        [@Duration],
        ASC
    ),
    'Table'[reason]
)
    Title = 
VAR n = SELECTEDVALUE ( 'nth Largest'[n] )
RETURN
    n & SWITCH ( n, 1, "st", 2, "nd", 3, "rd", "th" ) & " Largest Reson"

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi vamsi_tiger 
    Please refer to attached sample file with the proposed solution

    nth Largest Reason = 
MAXX ( 
    TOPN (
        1,
        TOPN ( 
            SELECTEDVALUE ( 'nth Largest'[n] ),
            SUMMARIZE ( 
                'Table',
                'Table'[reason],
                "@Duration", SUM ( 'Table'[duration] )
            ),
            [@Duration]
        ),
        [@Duration],
        ASC
    ),
    'Table'[reason]
)
    Title = 
VAR n = SELECTEDVALUE ( 'nth Largest'[n] )
RETURN
    n & SWITCH ( n, 1, "st", 2, "nd", 3, "rd", "th" ) & " Largest Reson"