How to get the second top performer

[Anonymous]

Hello,

I have 4 columns: TV channel, Show, Country, Language

I am trying to display in a card the TV channel with the second highest number of TV shows count (for example I would like to display "CNN" if it's the TV channel with the second higher count of TV shows). I apply two slicers on that card based on country and language.

My current approach has been to compute a RANKX() measure on TV channel based on COUNT(TV show). Then, I tried to filter my card with the TV channel based on that measure, setting Rank = 2 but I cannot interact with the filter (neither click on it nor write anythign in it).

My workaround at the moment is to use a table. I don't know why but in a table I can filter based on my rank while in a card I cannot. Nonetheless, the layout is not super clean as I have to hide the column header.

So my question is, do you have any idea on how to get the second top TV channel in term of TV show count?

Thanks for your help,

[TomMartens · Accepted solution]

Hey @Anonymous ,

this is how my sample data looks:

I created 2 measures:

channel ranked = 
RANKX(
    ALLSELECTED('Table'[Channel])
    , CALCULATE(
        SUM('Table'[Count([TV Show]])])
    )
)

and

channel filtered by rank = 
CONCATENATEX(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    VALUES('Table'[Channel])
                    , 'Table'[Channel]
                )
                , "rk" , [channel ranked]
            )
            , [rk] = 2
        )
        , [Channel]
    )

I used the 2nd measure on the card visual:

Hopefully, this is what you are looking for.

Regards,

Tom

[Anonymous]

Thanks a lot, it works perfectly.

How would you do it to apply the same solution but to display the COUNT(TV show) instead?

BEst,

Olivier

[TomMartens]

Hey @Anonymous ,

I changed the 2 nd measure a little:

channel filtered by rank = 
CONCATENATEX(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    VALUES('Table'[Channel])
                    , 'Table'[Channel]
                )
                , "cnt" , CALCULATE(SUM('Table'[Count([TV Show]])]))
                , "rk" , [channel ranked]
            )
            , [rk] = 2
        )
        , [Channel] & ": " & [cnt] 
    )

I added the column [Count(TV Show)] using  CALCULATE(SUM(...)) if the count in your example already is a measure just use the measure reference [measure] name inside the ADDCOLUMNS(SUMMARIZE(...), "cnt", ...

This allows to use this column inside the CONCATENATEX. In my example I still use the channel column and a little text snippet (": "). My Card visual will look like this:

Hopefully, this is what you are looking for.

Regards,

Tom

[Anonymous]

Hello,

Thanks. It works almost perfectly. Nontheless, I don't know why, it prints twice the value. instead of 2065 I have 20652065 and instead of 10.5 I have 10.510.5. Please find below the query I used.

2_value_fresh =

CONCATENATEX(

FILTER(

ADDCOLUMNS(

SUMMARIZE(

VALUES(REPEATS_EVENTS[ChannelName])

, REPEATS_EVENTS[ChannelName]

)

, "cnt" , CALCULATE(SUM(REPEATS_EVENTS[Fresh Hours]))

, "rk" , [Rank_Fresh]

)

, [rk] = 2

)

, [cnt]

)

Thanks for your help,

Olivier

[TomMartens]

Hey @Anonymous ,

this is because there are 2 rows that satisfy the condition
[rk] = 2

This means you have to check the rank, and maybe you have to use a 2nd summarize that uses the FILTER(...) as input.
You can also consider to change the CONCATENATEX function to SUMX or AVERAGEX, whatever meets your use case

Hopefully, this provides some ideas.

Regards,
Tom

[amitchandak]

@Anonymous ,

assume this is rank measure

City Rank = RANKX(all(Geography[City]),[Sales])

Rank 2nd top = CALCULATE(if([City Rank]=2,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank 2nd top = sumx(VALUES(Geography[City]),if([City Rank]=2,[Sales],BLANK()) )

Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )

Also refer

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814