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:
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...)