Using Multiple Sort Expressions

[#1]

Hi All,

I am currently using the following expression

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'91+ Days Late'}>}[Open Balance])

to sort open balances by amount of 'red dollars'.

As background, I have 5 buckets that fall under the field [AgingGroups]:

IF(isNUll([Aging]),'Current',
IF(Aging>=1 and Aging <=30,'1-30 Days Late',
IF(Aging>30 and Aging <=60,'31-60 Days Late',
IF(Aging>60 and Aging<=90,'61-90 Days Late','91+ Days Late')))) AS [AgingGroups]

Where [Aging] is just a column in an excel sheet that displays the number of days the balance is past due.

These buckets are displayed on the front end as colors: dark green, light green, yellow, orange, and red.

Currently, I have sorted the open balances by most red dollars to least red dollars with the expression above (Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'91+ Days Late'}>}[Open Balance]) ). However, once there are zero red dollars, the open balances are sorted in alphabetical order by client. I want it to sort by red dollars, and then if there are no red dollars, sort by orange dollars, etc, all the way until the dark green.

How would I go about doing this in my Sort Expression? Am I allowed to sort by multiple expressions? Simply typing

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'91+ Days Late'}>}[Open Balance])

+

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'61-90 Days Late'}>}[Open Balance])

+

...

does not work.

Suggestions?

Thank you,

Melanie

[sunny_talwar · Accepted solution]

You can try like this:

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'91+ Days Late'}>}[Open Balance]) +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'61-90 Days Late'}>}[Open Balance])/1E4 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'31-60 Days Late'}>}[Open Balance])/1E8 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'1-30 Days Late'}>}[Open Balance])/1E12 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'Current'}>}[Open Balance])/1E12

Basically giving more weight to first expression and giving lessor weight to other expressions

[#3]

This worked. But can you explain why? I was trying to do a multiplier. So the same concept with the weighted values where I multiplied by either 1000, 100, 10, 1, or 0. And how does this work without messing up the Open Balance values?

[sunny_talwar]

Should have worked the same way with your multiplication depending on the values of the above expressions. I just added some extra weighting by dividing by 10000, 100000000, 1000000000000, 10000000000000000.

I just noticed that I had 1E12, may be change that to 1E16 or 1E14 (not sure if 1E16 can be used)

[#5]

I did change it to 1E16, and it worked, thanks. Maybe my multipliers weren't spread out enough so the total dollars still allowed open balances with high orange values to pass the red, etc.

[sunny_talwar]

Ya, that's what I am thinking as well. But I am glad it worked out well in the end