How to sort a dimension by highest revenue using Dual?

[p0cket0m]

I have a dimension called BusinessDivision which is used in a filter pane. 

I want to display the values by highest revenue first, those should be on top of the filter list.

I tried

=dual(BusinessDivision, rank(sum(Revenue)))

but while the formula editor says its ok, in the filter pane I get 'Invalid dimension' error.

What am I doing wrong?

[salonicdk28]

Hi,

Can you try sorting  by expression in that dimension field filter pane and in expression write sum(Revenue) and then see if it works

Thanks,

Saloni

[salonicdk28]

Hi,

Can you try sorting  by expression in that dimension field filter pane and in expression write sum(Revenue) and then see if it works

Thanks,

Saloni

[p0cket0m]

Hi Saloni,

Thanks for your fast reply. Sorting by expression with sum formula works 😊

But how would it work with a master-dimension and using dual? That way would be better because our worksheet designers then dont need to think about it everytime they use it. It would sort always by revenue with automatic sorting.

[salonicdk28 · Accepted solution]

Use aggr in your dual expression -

=aggr(dual(BusinessDivision,rank(sum(Revenue)),BusinessDivision)

[p0cket0m]

This is exactly what I was looking for.

Thanks!