Top & Bottom 10 based on % Week on Week changes

[Anonymous]

Hello,

I'm reporting on a review platform and I've got Top 10 and Bottom 10 sites based on average review rating, however if there are more than 10 sites with a score of 5 it brings them all back, is there a way of bringing back based on number of reviews too?

Secondly I'd like to be able to show sites that were in the bottom 10 last week and aren't in it anymore and potentially look at those that have been in the bottom 10 for more than 1 week.

What would be the best way to do this? I've tried using RANKX but can't get it to work. 

[v-zhenbw-msft]

Hi @Anonymous ,

Sorry for that we don’t understand your requirement clearly.

Do you want the top 10 sites name and the rate is greater than the average rate? We create a sample and you can refer it.

1. Create a calculate column.

Column = IF([Rating]>=[Average Rating],1,0)

2. Then we can create a table visual and use Filters on this visual function.

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.     

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

Hi @v-zhenbw-msft Thanks for your reply, unfortunately that's not what i'm after. The rating column is the number of ratings a site has and the average rating column is the rating that the site received. What I want to see is the following:

- Top 10 & Bottom 10 sites based on average rating but also based on how many ratings they have. My issue is that some sites only have 1 rating of 5 so they're included in the Top 10 alongside those that have had many more but the Top N filter only lets you sort on one metric.

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can create a Calculated Column for grouping the rankings.

Ratings Group =

SWITCH

(

TRUE(),

'Table'[Rating] >= 20 , "20 and above",

'Table'[Rating] < 20 && 'Table'[Rating] >= 15, "15 to 19",

'Table'[Rating] < 15 && 'Table'[Rating] >= 10, "10 to 14",

"9 and below"

)

Then you can create a measure

RankingGroup =

RANKX(FILTER(ALL('Table'[site], 'Table'[Ratings Group]), 'Table'[Ratings Group] = MAX('Table'[Ratings Group])),CALCULATE(SUM('Table'[Average Rating])))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)