cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
12Bowers12
Resolver II
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
TheoC
Memorable Member
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!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

Shishir22
Resolver III
Resolver III

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! 

View solution in original post

CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-09-30 061950.png

View solution in original post

8 REPLIES 8
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-09-30 061950.png

View solution in original post

Thank you, CNENFRNL.

Thank both of you, the two solutions work perfectly.

TheoC
Memorable Member
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!

Want to connect?  www.linkedin.com/in/theoconias

Shishir22
Resolver III
Resolver III

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! 

View solution in original post

TheoC
Memorable Member
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!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

TheoC
Memorable Member
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!

Want to connect?  www.linkedin.com/in/theoconias

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.