2 Replies Latest reply on May 3, 2013 6:33 AM by Harry Alverson

# Top 10 (n) of the bottom 20% (if greater than \$100k)

I've been scouring the help board for pre-existing answer but can't seem to find one. The precise words to accurately describe this issue are also eluding me (interim ranking?)

I have a data set with business partner revenues by city. (image below) I've managed to use various table calcs (running sums and index) to isolate the top performers representing 80% of revenues vs the bottom performers representing remaining 20% of revenues.

I am now looking to create a view that isolates the "top of the bottom" performers... and only have them appear if their revenues exceed a certain dollar threshold. Since this info feeds through to a dashboard, and since different cities have different numbers of partners, the ability to do this in a dynamic automated way is proving too complex for me to solve on my own.

I feel certain the right combination of FIRST(), LAST(), or SIZE() or some other table cal should be able to do the trick. I just don't understand them well enough to ferret out the answer. I'm not beholden to any one solution, but here's a brief description of what I have in place so far:

To ascertain bottom 20% status, I have two calculations, the second draws on the first to create a true/false:

1)   "Ptr 2012 Running Sum" = RUNNING_SUM(SUM([Revenues])) / TOTAL (SUM([Revenues]))

2)   "Is Top 80%" =  [Ptr 2012 Running Sum]<.80

I threw the 2nd one into my filters and selected "false"

I then isolated the portion of the bottom 20% with over \$100k revenues using another table calc:

LOOKUP(sum([Revenues]),0)

put this into filters and select "at least" 100k

However, I cannot figure out how to take the remaining info and systematically isolate just the top 10 partners within this criteria.

• Using a top 10 filter for revenue on the "partner name" dimension doesn't work because it preempts the table calculations.
• And since the starting rank/number of both the first and last partners in the "bottom 20%" set changes between cities, I can't figure out how to nail down something that is dynamically referential to work with.

Bottom 20% partners and their revenues in City A: (top 80% partners, and bottom 20% ptrs with revenues below \$100k already filtered out...)

• ###### 1. Re: Top 10 (n) of the bottom 20% (if greater than \$100k)

Hi Harry,

There really should be an easier way to do this, but building on what I think you've done above, I might add another calculated field that creates an index for the bottom values. Then you can filter on this index.

Count of bottom values =

IF [Is Top 80%] THEN 0 ELSE 1 + PREVIOUS_VALUE(1) END

This is a nested table calculation, since [Is Top 80%] is based on the [Ptr 2012 Running Sum] table calc. After adding this field to the view---which I'd do first to test that it's working---make sure you verify the [Ptr 2012 Running Sum] table calc's compute using by right-clicking on the pill > Edit Table Calculation > in the upper pull-down box for "Calculated Field".

Now Count of bottom values should be 1 for the first "bottom value" and then 2, 3, 4, ... and you can add a filter for the top 10. ...

Jim

• ###### 2. Re: Top 10 (n) of the bottom 20% (if greater than \$100k)

Jim, thanks for the reply. It worked!! Thanks for the insight.