Sort a calculated dimension in a bar chart

[#1]

Hello,

I need your help if possible. I have created a bar chart that uses a calculated dimension for the X-axis. The calculated dimension is:

=if([Gross Sales]<10000000 ,'<$10MM',

if(([Gross Sales]>=10000000 and [Gross Sales]<25000000),'$10MM-$25MM',

if(([Gross Sales]>=25000000 and [Gross Sales]<50000000),'$25MM-$50MM',

if(([Gross Sales]>=50000000 and [Gross Sales]<75000000),'$50MM-$75MM',

if(([Gross Sales]>=75000000),'>$75MM','None')))))

The Y-axis shows the number of products, so the bar chart shows correct values. It shows the number of products for each Gross Sales range. The problem is that I am not able to sort the view.

I want the X-axis to show in this order (ascending):

'<$10MM','$10MM-$25MM','$25MM-$50MM','$50MM-$75MM','>$75MM'

I am getting a different order, see the attached screenshot.

I have tried the following:

match(

if([Gross Sales]<10000000 ,'<$10MM',

if(([Gross Sales]>=10000000 and [Gross Sales]<25000000),'$10MM-$25MM',

if(([Gross Sales]>=25000000 and [Gross Sales]<50000000),'$25MM-$50MM',

if(([Gross Sales]>=50000000 and [Gross Sales]<75000000),'$50MM-$75MM',

if(([Gross Sales]>=75000000),'>$75MM','None'))))), '<$10MM','$10MM-$25MM','$25MM-$50MM','$50MM-$75MM','>$75MM')

But doesn't work. Any help will be very appreciated.

Thanks,

[#2]

Have you tried putting your expression as a variable, say vSalesCat?

Then you could do match($(vSalesCat),'<$10MM','$10MM-$25MM','$25MM-$50MM','$50MM-$75MM','>$75MM').

[#3]

I have tried your suggestion and still doesn't work. The bars change of position when I change from ascending to descending but still not in the order wanted.

Thanks,

[#4]

Hmm. Is Gross Sales a calculated field? If you replace it with sum(ALL sales) or whatever the calculation is in the variable, that could possibly help... unfortunately, I don't know for sure.

[#5 · Accepted solution]

Hi,

Go to the sort tab in your chart and highlight the calculated dimension and promote it to the top of the list. Select sort by 'Expression'.  Select 'Ascending' from the drop down and then type [Gross Sales] in the expression box.  Hope that helps!

-ART

[#6]

This fixed it. Thank you very much.