Forum Discussion
Sort matrix/table by latest month column
Hi all,
I am trying to sort a table containing product production per month. I want the top row to have the product which was produced in largest amount in latest/current month. When I do traditional sort by column, I am getting rows sorted according to sum of values of all months. Is there any way to achieve desired output below?
Months are in one measure
Data:
| Product | Oct | Nov | Dec | Jan |
| A | 2 | 6 | 2 | 2 |
| B | 3 | 2 | 1 | 4 |
| C | 2 | 4 | 1 | 2 |
Desired output:
| Product | Oct | Nov | Dec | Jan |
| B | 3 | 2 | 1 | 4 |
| A | 2 | 6 | 2 | 2 |
| C | 2 | 4 | 1 | 1 |
Oldnewbie in this case you can achieve it by creating a calcualted table like below. You can apply year and month filter based on the current date and make filter dynamic as well.
in this case i have order table which has transactional level data for each product and its category. from this table i have creted a summarize table like below
Table_Product_rank = SUMMARIZECOLUMNS(Orders[Product Category],FILTER('Calendar',YEAR('Calendar'[Date].[Year]=2022)),FILTER('Calendar','Calendar'[Month#]="12"),"Sales",SUM(Orders[Sales]))then added rank calc column in this tableRank = RANK.EQ(Table_Product_rank[Sales],Table_Product_rank[Sales],DESC)this table will alwasy be sorted based on the latest month volume like belowyou can sort the above table on the rank category
then you can link this table to your main order table,
then in your matrix visual you can use the category from the newly created table and data will be sorted based on the data in the new table.
have attached the pbix file as well.
2 Replies
Oldnewbie in this case you can achieve it by creating a calcualted table like below. You can apply year and month filter based on the current date and make filter dynamic as well.
in this case i have order table which has transactional level data for each product and its category. from this table i have creted a summarize table like below
Table_Product_rank = SUMMARIZECOLUMNS(Orders[Product Category],FILTER('Calendar',YEAR('Calendar'[Date].[Year]=2022)),FILTER('Calendar','Calendar'[Month#]="12"),"Sales",SUM(Orders[Sales]))then added rank calc column in this tableRank = RANK.EQ(Table_Product_rank[Sales],Table_Product_rank[Sales],DESC)this table will alwasy be sorted based on the latest month volume like belowyou can sort the above table on the rank category
then you can link this table to your main order table,
then in your matrix visual you can use the category from the newly created table and data will be sorted based on the data in the new table.
have attached the pbix file as well.
My requirement to make dynamic...
you have to use year = 2022 and month = 12
Can you please elaborate this and suggest solution.
Thanks in advance
