Rank sub category using Calcs to get things like Top 10 subcategories

This can be a bit brittle since it requires a sort be maintained (see last section if you want to remove this brittle point), but in certain situations is valuable to have with a quick formula. Common use case would be to set this up and then filter on the calc to get top 10

Setup:

  1. Primary Category dimension (Country in this example)
  2. Secondary Category dimension (State in this example)
  3. Metric to rank with (User count in this example)
  4. Sort first on Primary, then secondary sort (shift+click) on the metric to get descending. These sort conditions must stay applied or the formula will not work

Formula:

=IF((ROW() = 1), 0, IF((A2 = A1), COUNTIF(A$1:A1, A2), 0)) + 1

Input this by double clicking on the second row of the new calc. The offset reference will error if you input on first line

Output

And you can see it detects the point at which UK ends and USA begins (why the sort is required for this to work)

Getting Top 10

Just filter on your rank calculation for <11 and boom, you have top 10 subcategories of each primary category without any modeling :slight_smile:

Convert the calc for more robust analysis
And, if you want to a little further you can easily do even more cool stuff like this:

  1. From the workbook tab Model > Requery result to get your query as a view
  2. In the field picker on state, select Aggregates > List from the field options
  3. Select State Rank, and State List, then pivot on Country to get a nice table of your top 10s