Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Top & Bottom 10 based on % Week on Week changes

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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)