cancel
Showing results for
Did you mean:
Resolver II

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

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" )
3 ACCEPTED SOLUTIONS
Memorable Member

@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.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Resolver III

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.

Cheers!
Community Champion
``Month Group = CEILING( Loss[Days Claim]/30, 1 ) & " M"``

8 REPLIES 8
Community Champion
``Month Group = CEILING( Loss[Days Claim]/30, 1 ) & " M"``

Resolver II

Thank you, CNENFRNL.

Resolver II

Thank both of you, the two solutions work perfectly.

Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Resolver III

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.

Cheers!
Memorable Member

@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.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Resolver II

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

Announcements

#### Launching new user group features

Learn how to create your own user groups today!