sort calculated dimension by expression

[weiw]

Hi,

I create a calculated dimension:

floor(aggr (sum (sales),[CustomerID]),100)&'-'&(floor(aggr (sum (sales),[CustomerID]),100)+99)

as a range of sales amount per customer. And put it as dimension in a bar chat.

The dimension would be 0-99,100-199,200-299,....,500-599,...1000-1099,...30000-30099,..., the list would be dynamic

The questions is how to sort this dimension ascending by the value of the range of sales? Thanks in advance.

[gomeri]

Hi @weiw ,

you can create a master measure with your formula and then (with adding of dimension) in the bar chart use sort numerically -> ascending

Thanks

[Kushal_Chawda · Accepted solution]

@weiw  create your dimension with dual() as below. Then in sorting option, sort your dimension numerically ascending or descending

=dual(floor(aggr (sum (sales),[CustomerID]),100)&'-'&(floor(aggr (sum (sales),[CustomerID]),100)+99),
floor(aggr (sum (sales),[CustomerID]),100))

[weiw]

Spoiler

I also found that solution. Thank you.

[weiw]

Sorry I clicked the wrong button. I tried this,also tried to use the lower bound of floor function as expression to sort, but not working. I found a solution which is same as the one from Kushal. Thanks.