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)

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

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'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)