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 date reason duration 23/6 breakdown 5 24/6 snacks 10 22/6 breakdown 2 21/6 shutdown 50 21/6 other 1 24/6 other 2 24/6 break 5 so i...
  • 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"
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"