Sort by expression

[Anonymous]

Hello,

I am trying to sort by expression, but it doesn't appear to be working. I have a column with dollar values, column with categories, and a column with Zones. A Zone may appear more than once.

Anyways I made the Zone column a Dimension and the dollar value column a measure. The dollar value column needs to be broken into 3 categories based on another column. So I have 3 measures as shown below:

Sum({<[Category]={"Category 1"}>} "Dollar Value")

Sum({<[Category]={"Category 2"}>} "Dollar Value")

Sum({<[Category]={"Category 3"}>} "Dollar Value")

I have the Zone measure all the way top in the sorting screen and have selected "Sort by Expression". I then have the formula:

Sum("Dollar Value")

I have insured all the sorting options are turned off except for the Zone sort, and yet it isn't sorting correctly. I want the Zone positioning to be in the order of highest dollar values at the start.

Any ideas?

[juraj_misina]

Hello Christopher,

it seems to me as if there were records with Category other then 1, 2, or 3. Try adjusting the sorting formula to include set analysis for those cagetories:

Sum({<[Category]={'Category 1','Category 2','Category 3'}>} [Dollar Value])

Hope this helps.

Juraj

[Anonymous · Accepted solution]

Hey, I appreciate your response, but it wasn't quite correct. However, it led me in the right direction. Thank you!

The problem was that I had too many brackets in Category 2's formula in the measure section.

Sum({<[[[Category]]]={"Category 2"}>} [Dollar Value])

Should have been

Sum({<[Category]={"Category 2"}>} [Dollar Value])

I am not sure why it impacted the sort like it did. The numbers were correct in the chart so it was clearly pulling the numbers fine. Just could sort.