Attached is my sample solution. The workbook is in version 10.1.3, so you may not be able to open it.
Here is what I did:
1. Create a table calc [Revenue by tenant] to sum up the total revenue by tenant. (Make sure the "Compute Along" is set to all fields except [Tenant Name].)
2. Create a table calc [Tenant rank] to rank the total revenues. Configure it to compute along all fields, but at the level of [Tenant Name].
3. Create a field [In Top N] to compare against your parameter and act as your filter. (I created the parameter [Top N], too, since your sample did not have one.)
4. Place [In Top N] on your Filters shelf, and filter on True.
So… the above is not quite there. For some reason, occasionally individual rows for a tenant will carry a different RANK() than the other rows for that tenant. For instance, "Michells" has one row that shows rank 4 rather than rank 5. And Wallmart has a row that shows rank 5 instead of rank 6 — so it's the only row that shows on a "Top 5" filter.
I'm not exactly sure what's going on there, and I couldn't figure out how to reconfigure the table calc to compensate. I'm going to let me subconscious mull over it and see if something comes to mind.
Thanks Jamie for the reply, please see if you can find some thing which can solve the issue.
Attached is a revised workbook. I finally got it working, but unfortunately, I ventured into the realm of, "I don't exactly know what I did or why I finally got it working, but it's working — everybody hold your breath".
I'm not going to do an in-depth, but here are a few highlights of what I changed:
- I used RANK_DENSE() instead of RANK() so that I could simplify the table calc's "compute along" to a straight Table(down), while still coming up with a single number per Tenant. This fixed the problem with some tenants having a single row whose rank varied from all the rest of the rows.
- In my filter [In Top N], I had to reference [Tenant Rank] with a LOOKUP(). I'm not exactly sure why. But if I didn't use the LOOKUP(), [Tenant Rank] seemed to evaluate differently in the [In Top N] filter than it did when I was monitoring it in the Measure Values shelf.
Please review the attached workbook, paying special attention to the "Compute Along" configuration for each table calc.