Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |