Top list of siblings while keeping the original sort order

You want to create a list of the Top 5 Salespersons within each Territory, and at the same you want to keep the original (alphabetical) sort order of the Territories: Asia, Europe and North America.

This is the default table with the three territories and Salespersons:

First, you will need to sort by the Customer Territory columnĀ and by the Revenue column:

  1. Sort the Customer Territory column by clicking the sort arrow in the column header
  2. Hold your CTRL key while clicking the sort arrow (twice) of the Revenue column header

This method will produce a primary and a secondary sort order in your table:

Now, you should add a visibility agent (Hide members) with this condition:

allcount(d1, all(c), m1) = 0 and allcount(d1, d1:0(s), m1) > 5

This will produce this result:

Notice that the subtotals and the grand total does not reflect the sums of the now visible members. To fix this, you may want to introduce a couple of additional calculated columns:

Calculation c1:

if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m1) else sum(d1, all(v,c), m1)

Calculation c2:

if allcount(d1, all(c), m1) = 0 then sum(c1, 0, m1) else sum(c1, all(v,c), m1)

This will produce a result like this, where the c2 column contains the correct subtotals and the correct grand total:

Eventually, you can go to the 'Visibility' options in the Properties Smartpad to hide the original measure and to hide the c1 calculation, and the c2 column may be renamed to a better column header.




Please sign in to leave a comment.

Didn't find what you were looking for?

New post