How to create top5 + favorite company + other table
I am looking for a dynamic way to create a table that would like this:
I want to be able to always have the top 5 companies in the table and always show my favorite company (even if it is not within the top5). The top5 companies are not always the same depending on filters chosen, so I would need a way to dynamically select for top5 companies. Using the ValueList function appears to be not the right way as it seems static.
Re: How to create top5 + favorite company + other table
I built an example this time, because I was thinking, "Why wouldn't aggr() and rank() give me the right rank?" They worked fine for me. Ah! I think I see the problem now that I'm reading more closely. You said "Aggr() within Rank()", where it needs to be the other way around. See attached. The expression is this:
The idea here is to ignore our calculated dimension with its seven values, and instead aggr() by Company so as to include all companies. I think of aggr() as building a chart internally. So we've basically built a chart of all companies, not just the ones we want to see. The expression inside the aggr() is our expression for this internal chart, so now we're ranking sum(Sales) by company but including all companies. Finally, we map these numbers back to our original and displayed chart, and find that only the top five and our favorite company are being displayed, so only fill in those numbers.