TOPN Across Multiple Columns

[DLB13]

Hi all - 

I am new to PowerBI - I know the measure I need to use (TOPN), but I can't quite figure out how to set it up properly.

I have ~20 columns across which I need to identify and display (as text) the top three selections. I've attached a really basic example of how the data looks. So in this case, if this were sports that high schoolers played, I would need to have a measure to calculate and display the column name for the top three most commonly played sports.

Any tips?

Respondent	Soccer	Football	Golf	Baseball	Hockey	Track
A	Soccer				Hockey	Track
B	Soccer			Baseball
C		Football		Baseball
D	Soccer			Baseball	Hockey
E			Golf
F	Soccer			Baseball		Track
G	Soccer	Football				Track
H		Football		Baseball
I		Football

[v-yingjl · Accepted solution]

Hi @DLB13 ,

1. Select the 'Respondent' column and right click to unpivot other columns in power queries, close and apply it.

2. Create a measure like this:

TOP3 =
VAR tab =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[Attribute] ),
        "Count",
        VAR attr = 'Table'[Attribute]
        RETURN
            COUNTROWS (
                FILTER ( ALL ( 'Table' ), 'Table'[Attribute] = attr && 'Table'[Value] = attr )
            )
    )
VAR newtab =
    ADDCOLUMNS ( tab, "Rank", RANKX ( tab, [Count],, DESC, DENSE ) )
RETURN
    CONCATENATEX ( FILTER ( newtab, [Rank] = 1 ), [Attribute], "," ) & " "
        & CONCATENATEX ( FILTER ( newtab, [Rank] = 2 ), [Attribute], "," ) & " "
        & CONCATENATEX ( FILTER ( newtab, [Rank] = 3 ), [Attribute], "," )
​

3. Put the measure in a card visual and you will get the top3 sports:

Here is the sample file that hopes to help you, please try it: PBIX 

In this sample file I also created a rank table for reference that you can refer.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yingjl · Accepted solution]

Hi @DLB13 ,

1. Select the 'Respondent' column and right click to unpivot other columns in power queries, close and apply it.

2. Create a measure like this:

TOP3 =
VAR tab =
    ADDCOLUMNS (
        DISTINCT ( 'Table'[Attribute] ),
        "Count",
        VAR attr = 'Table'[Attribute]
        RETURN
            COUNTROWS (
                FILTER ( ALL ( 'Table' ), 'Table'[Attribute] = attr && 'Table'[Value] = attr )
            )
    )
VAR newtab =
    ADDCOLUMNS ( tab, "Rank", RANKX ( tab, [Count],, DESC, DENSE ) )
RETURN
    CONCATENATEX ( FILTER ( newtab, [Rank] = 1 ), [Attribute], "," ) & " "
        & CONCATENATEX ( FILTER ( newtab, [Rank] = 2 ), [Attribute], "," ) & " "
        & CONCATENATEX ( FILTER ( newtab, [Rank] = 3 ), [Attribute], "," )
​

3. Put the measure in a card visual and you will get the top3 sports:

Here is the sample file that hopes to help you, please try it: PBIX 

In this sample file I also created a rank table for reference that you can refer.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@DLB13 , unpivot the data. 

https://radacad.com/pivot-and-unpivot-with-power-bi

Use Rank post that

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[glissando]

Looks like your data comes from a pivoted report. Unpivot the data using Power Query so that it is in proper databse format. That will allow you to perform ranking.

Thanks