Creating a Ranking List/Table in Power BI Desktop

[Anonymous]

Hi,

I have a dataset that will eventually be populated by survey results. The survey asks a question in which the user will select five out of about twenty predetermined "community concerns" that they feel is most relevant to their own community. The user will not rank their concerns, just select them.

I'm looking to create a visual that sums the count of each response in all five columns and creates a list of the five most common concerns in order. So, if "burglary" is the most commonly chosen concern across all survey responses, burglary would report at the top, and so on.

I've attached a screenshot of sample dummy data I'm using to build the report but I'm not having much luck with this one. Any guidance would be greatly appreciated.

Thanks!

[HashamNiaz]

Hi @Anonymous !

Please explain do you want to list Top Concerns across all columns or you want to list Top 5 concerns from [Concern1] - [Concern5] columns;

You can use below DAX to create Ranking measure and then you can place them to filter out Top 5 concern for Any column;

Rank Concern 1 = RANKX(ALL(Table), CALCULATE(COUNT(Table[Concern1])), , DESC, Dense)

Rank Concern 2 = RANKX(ALL(Table), CALCULATE(COUNT(Table[Concern2])), , DESC, Dense)

You can replace Table with your Table Name and Concern1 & Concern2 with your Concern column names.

First measure will Rank All the concerns in Concern 1 column, you can then limit first 5 concerns using this measure and put them into table.

Second measure Rank All the concerns in Concern 2 column, you can then limit first 5 concerns using this measure and put them into table

Regards,

Hasham

[Anonymous]

Would there be a way to list the top five concerns selected across all five columns collectively, not each column separately?