Ranking across multiple columns

[Anonymous]

Hi! I'm new to Power BI.

I have survey findings of sentiments (1 - 5) for 10 categories. There are 1000 respondents with additional data such as location, age and gender. I end up with 10 columns of ratings with 1000 rows.

First, how do I find the Top 3/ Bottom 3 rated category across the 10 columns? Each category rating is calculated by average.

Second, can I add slicers to the data set and have it change accordingly to location/age/gender?

Sample data here

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

If it is OK with you, please share the sample pbix file or sample data.

It will be very much helpful if I can see the sample to come up with a desirable outcome with possible solutions.

Thank you very much.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

If it is OK with you, please share the sample pbix file or sample data.

It will be very much helpful if I can see the sample to come up with a desirable outcome with possible solutions.

Thank you very much.

[Anonymous]

Hi, sample data here: https://drive.google.com/file/d/13h07LspXAQ7e_CM0MDcQ3-WkCfHWqbrx/view?usp=sharing Thank you!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous  

Thank you very much for sharing.

I hope I come up with what you needed.

I unpivoted your original table to look like the below in order to calculate numbers per each category.

Then I wrote two measures like below.
You can also check some other measures in the PBIX file that I have connected to the link below.

Avg. Rating Ranking Desc =

RANKX( ALL(Data[Category]), [Avg. Rating],,DESC)

Rank description =

SWITCH( TRUE(),

[Avg. Rating Ranking Desc] = 1, "Top 1",

[Avg. Rating Ranking Desc] = 2, "Top 2",

[Avg. Rating Ranking Desc] = 3, "Top 3",

[Avg. Rating Ranking Desc] = 10, "Bottom 1",

[Avg. Rating Ranking Desc] = 9, "Bottom 2",

[Avg. Rating Ranking Desc] = 8, "Bottom 3",

BLANK())

https://www.dropbox.com/s/v4z50zbfwwnhche/AC1316%202021%200322.pbix?dl=0 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[Anonymous]

This works perfectly! Thank you - marked as solution