Second top value

[dsmitha]

Hi,

Request your help to overcome this issue

From my table I could easily find out the Top position value by applying the MAX function but how can i get the second top value . It works when I use TOP N function in the Filter pane but i would like to get the same via DAX

awaiting your guidance

dsmitha

[amitchandak · Accepted solution]

@dsmitha , Try these measures

Rankx = ankx(allselected(Table[Category]), [sales],,desc,dense)

You can use visual level filter Rank =2 (<category should be replaced with your rank column> should be in visual)

or create a measure on top of that

2nd Rank sumx(filter(values(Table[Category]), [Rank] =2), [Sales])

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

One of ways to check the second top value is, MAX function can be still utilized to find second top value when the first MAX value is filtered out.

Value total: = 
SUM( Data[Value] )

Value first max: = 
VAR _condition =
    CALCULATE ( MAX ( Data[Value] ), ALL ( Data[Category] ) ) = [Value total:]
RETURN
    DIVIDE ( _condition, _condition ) * [Value total:]

Value second max: = 
VAR _condition =
    CALCULATE (
        MAX ( Data[Value] ),
        FILTER ( ALL ( Data[Category] ), [Value total:] <> [Value first max:] )
    ) = [Value total:]
RETURN
    DIVIDE ( _condition, _condition ) * [Value total:]

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

One of ways to check the second top value is, MAX function can be still utilized to find second top value when the first MAX value is filtered out.

Value total: = 
SUM( Data[Value] )

Value first max: = 
VAR _condition =
    CALCULATE ( MAX ( Data[Value] ), ALL ( Data[Category] ) ) = [Value total:]
RETURN
    DIVIDE ( _condition, _condition ) * [Value total:]

Value second max: = 
VAR _condition =
    CALCULATE (
        MAX ( Data[Value] ),
        FILTER ( ALL ( Data[Category] ), [Value total:] <> [Value first max:] )
    ) = [Value total:]
RETURN
    DIVIDE ( _condition, _condition ) * [Value total:]

[dsmitha]

I got the visual as i wanted , thanks a lot 👍

[dsmitha · Accepted solution]

Hi Jihwan_Kim,

excellent idea, this one also worked,  thanks a lot

PREVIEW

[amitchandak · Accepted solution]

@dsmitha , Try these measures

Rankx = ankx(allselected(Table[Category]), [sales],,desc,dense)

You can use visual level filter Rank =2 (<category should be replaced with your rank column> should be in visual)

or create a measure on top of that

2nd Rank sumx(filter(values(Table[Category]), [Rank] =2), [Sales])

[dsmitha]

Hi amitchandak,

Thank you very much for your quick response

that one solved my issue

regards,

dsmitha