5 Replies Latest reply on Jul 18, 2016 9:25 PM by Emmanuel Bruschi

    How can I calculate an average review score by category without breaking it down by sub category

    Emmanuel Bruschi

      I have 2 sets of writers who post reviews a blog. The 1st set is the general population of reviewers (all reviewers) and the 2nd is a set of expert reviewers (the best reviewers from identified from the general population of reviewer). I am trying to identify who the best reviewers are among the general population of reviewers and compare them to the expert reviewers to determine who I should exclude from my set of experts and replace by general reviewers. Since I have to weigh in the categories as well in order to represent accurately my product catalog, I have to break-down the analysis at the GL level (in other words "category").

       

      I worked on a model in excel hoping that I would be able to analyze the data in PowerPivot but the dataset is too large to be processed, so I am trying to work with Tableau, but I am new to it so what seems really hard for me might be a piece of cake for you guys

       

      Here is a sample of the data extract I have to work with (it includes both experts and general reviewers, identified by a "expert Y_N" field, experts are "1" and non experts are "0").

       

      Here is the sample PowerPivot I have created with definition for each calculated column. The end goal I have identified is to calculate both percentile columns.

       

      1/ Can you guys help me figure out how to reproduce a tableau file with the same format? I am currently blocked at the "GL ReviewAvg"; I cannot find a way to calculate it for the general population and not at the customer/reviewer level.

       

      2/I would like ideally to update my data source every month and automatically identify who are the bottom expert reviewers that should be replaced by top general population reviewers. That would mean I would have to identify (given a specified total number of reviewers I want included in my expert set) who are the best reviewers to include from the general population in my expert set and remove from the expert set. The idea is to know how many bottom experts are not as good as top general reviewers.

       

      I attached a sample file for you to be able to work with. Thanks a lot in advance, that will be so helpful!!!!!