4 Replies Latest reply on Jun 21, 2016 3:57 AM by Jamie Birch

    Grouping dimension values by ranking dynamically to show aggregate results without sorted or ranked list

    Jamie Birch

      Hi there,


      I'm working on a report at the moment to show some performance results for a series of websites across a few advertisers. I have looked around on the forum and noticed some helpful guides on grouping ranked list (which I've been able to do), but nothing yet on an overall grouping that is able to sit independently of table of values ranked by the number of orders. I was wondering if someone could help me to find a way to actually group by rank independent of a ranked table.


      Using the attached sample workbook I would like to achieve the following if its possible:


      • Show three groups as a dimension that can be filtered for:
        • The top 5 websites ordered by orders and aggregated (labelled "Top Tier")
        • The next 10 websites ordered by orders and aggregated (labelled "Mid Tier")
        • All remaining websites aggregated (labelled "Long Tail")
      • The ability to use a quick filter to select any of these three groups - pulling the data from the source and any filters applied at a later date such as specific months
      • To show the percentage of orders of the total orders split out by the three groups above


      Below is a table of how ideally I would need the data structured off the back of creating these three groups created as dimensions. I had a go at using a Level of Detail calculation to fix the website ID or group, but this didn't work for me either.


      Percentage of Total Orders
      Top Tier (top 5 websites by orders rank DESC)41630.01%
      Mid Tier (next 10 websites by orders rank DESC)58542.21%
      Long Tail (all remaining websites)38527.78%
      Total Orders1386100%


      I've also attached the .twbx packaged file with the grouping that work when showing the full list but not as in the table above, as well as the raw excel data source as an example - any help or tips to get this working would be greatly appreciated.


      Many thanks!