Forum Discussion
TOPN Across Multiple Columns
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 |
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
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-finaleHi 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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
