Community, I would love your help.
I have a complicated chart requirement. This is for a Mekko chart, but the problem applies to other types as well.
First, I have to show the top 5 in dimension 'Company' by sum(Amounts), regardless of selection, along with everything else lumped into 'Others'. However, I cannot just use Dimension Limits, because...
Secondly, if the user makes a selection, the selection(s) from Company must be added to that top 5. Furthermore, the selections must be sorted to the bottom, while the top 5 must remain sorted by sum(Amounts). 'Others' must remain at the top as well.
Currently, I am using a custom dimension:
=aggr(if(SubStringCount(Concat(Distinct GetFieldSelections(Company)), Company)>0,Company,
IF(RANK(SUM(Amounts)) <=5, Only({1}Company),'Others')),Company)
This is letting me break out my selections from 'Others', however, I cannot figure out the sort expression.
How do I keep Others on top, my selections at the bottom, and top 5 in the middle sorted by Amounts? I have attached a data and chart sample.
Ideally, if you selected companies E and F, I would want the order in the table and chart to be:
Others, A, J, H, I, C, E, F
Thank you!
