the column arranged in matrix from 1 Month to 12 Months order

[12Bowers12]

Hello everyone,

I have a calculated column to group days to 12 months as shown below. 

In the matrix, colomn [Month Group] functions as Columns. Logically, the colum in the matrix should be ranked from 1M to 12 M. However, the colum shows  the order as 1M, 10 M, 11M, 12M, then 2M to 9M. 

Any ideas to arrange the column from 1M to 12M?

Thank you.

Dennis

Month Group =

SWITCH(  TRUE(),

Loss[Days Claim] <=30, "1 M",

Loss[Days Claim] <=60, "2 M",

Loss[Days Claim] <=90, "3 M",

Loss[Days Claim] <=120, "4 M",

Loss[Days Claim] <=150, "5 M",

Loss[Days Claim] <=180, "6 M",

Loss[Days Claim] <=210, "7 M",

Loss[Days Claim] <=240, "8 M",

Loss[Days Claim] <=270, "9 M",

Loss[Days Claim] <=300, "10 M",

Loss[Days Claim] <=330, "11 M",

"12 M" )

[TheoC · Accepted solution]

@12Bowers12 in Power Query, create a Conditional Column that applies the same logic as the Month Group column but give it a value starting at 1 through to 12... Ensure it's a whole number and then when you Close & Apply, you can use the new column as the Sort by Column over the Month Group column.

[Shishir22 · Accepted solution]

Hi @12Bowers12 

Create one more calculated column as

Month Group Sort=

SWITCH(  TRUE(),

Loss[Days Claim] <=30, 1,

Loss[Days Claim] <=60, 2,

Loss[Days Claim] <=90, 3,

Loss[Days Claim] <=120, 4,

Loss[Days Claim] <=150, 5,

Loss[Days Claim] <=180, 6,

Loss[Days Claim] <=210, 7,

Loss[Days Claim] <=240, 8,

Loss[Days Claim] <=270, 9,

Loss[Days Claim] <=300, 10,

Loss[Days Claim] <=330, 11,

12)

Click on Month Group Calculated column. 

Click on SortBy. 

And select Month Group Sort.

Please mark this reply as solution if it fulfils your requirement. Kudos are also accepted.

Cheers! 

[CNENFRNL · Accepted solution]

Month Group = CEILING( Loss[Days Claim]/30, 1 ) & " M"

[12Bowers12]

Thank you, CNENFRNL.

[12Bowers12]

Thank both of you, the two solutions work perfectly.

[TheoC]

@CNENFRNL This is awesome! Tried to ❤ but only had 👍 available lol

[Shishir22 · Accepted solution]

Hi @12Bowers12 

Create one more calculated column as

Month Group Sort=

SWITCH(  TRUE(),

Loss[Days Claim] <=30, 1,

Loss[Days Claim] <=60, 2,

Loss[Days Claim] <=90, 3,

Loss[Days Claim] <=120, 4,

Loss[Days Claim] <=150, 5,

Loss[Days Claim] <=180, 6,

Loss[Days Claim] <=210, 7,

Loss[Days Claim] <=240, 8,

Loss[Days Claim] <=270, 9,

Loss[Days Claim] <=300, 10,

Loss[Days Claim] <=330, 11,

12)

Click on Month Group Calculated column. 

Click on SortBy. 

And select Month Group Sort.

Please mark this reply as solution if it fulfils your requirement. Kudos are also accepted.

Cheers! 

[TheoC · Accepted solution]

@12Bowers12 in Power Query, create a Conditional Column that applies the same logic as the Month Group column but give it a value starting at 1 through to 12... Ensure it's a whole number and then when you Close & Apply, you can use the new column as the Sort by Column over the Month Group column.

[TheoC]

@12Bowers12 can you use "Days Claim" value to sort "Month Group" which is under the Column Tools ribbon (the "Sort by Column" button)?

[12Bowers12]

Thank you, TheoC,

The message 'Sprt by another column' says:

We can't sort the 'Months Claim' column by 'Days Claim'. There can't be more than one value in 'Days Claim' for the same value in 'Months Claim'.

Any ideas?

Dennis