8 Replies Latest reply on Jan 1, 2016 1:06 PM by Jonathan Drummey

    Dynamic 'Top N and Others' grouping with further split based on sparse dimension

    Sukumar Semalaiappan

      Hello experts, I need to create Dynamic 'Top N and Others' grouping on my dashboard, say 'Top N and Other Containers' by Sales from Super Store use case. In addition, I need to show further break-up on Container level Sales by Department(using color as seen in the image below).

       

      I am using Table Calcs to keep the 'TOP N' members dynamic based on my Filters. The challenge I have is in calculating/showing the aggregate of Sales for "Others" container which should also have Department level break-up like 'Top N' containers.

      SuperStore-TopNandOthersWithDivision.jpg

       

      It is sparse data and the 3rd ranked Container, Jumbo Box, doesn't have Office Supplies and I don't get the aggregate for this department in there (Grouped Sales field). When I filter out the duplicate "Others" ('Large Box' and below) container based on the "Show?" filter, aggregate Sales for "Office Supplies" is ignored which is incorrect.

       

      How can we fix this? Attached the workbook..

       

      Thanks!

       

      PS: The ranking was done with the help of 'At the Level' option of table calc, here is a great post from Jonathan Drummey on this topic:

      At the Level – Unlocking the Mystery Part 1: Ordinal Calcs | Drawing with Numbers