11 Replies Latest reply on Feb 15, 2012 12:11 PM by Alex Kerin

    Count Distinct Aggregation

    John Mogielnicki

      Hello,

      It's going to be difficult to describe what I'm trying to accomplish, so I'm going to attach a packaged workbook and base my description off of that.  In the workbook, I have a category ID, and a sub-category id.  In the tab "Count Distinct," I have counted how many distinct Sub-Category IDs there are for each Category ID.  You can see that there are 4 Category IDs (3,4,5 and 6) that contain 1 Sub-category ID, 2 (1 and 7) that contain 3 Sub-Category IDs, and 1 (2) that has 6 Category IDs.  I want to show a view that counts the number of Category IDs that have a certain count of sub-category IDs.  So I would want it to be something like this (based on numbers in packaged workbook):

       

      Number of Sub-Categories within Category, Count

      1, 4

      3, 2

      6, 1

       

      Is this possible?  I'm assuming it would have to be a table calculation of some kind, but I can't figure out what it would look like.  This issue has come up multiple times so I figure it's worth asking about.

       

      Thanks in advance for any help.