Matrix Sort Help

[SWhiteMKL]

All,

I have a set of data that shows Sales by Group by Year.  I have the matrix arranges wil the row set as Group with Years nested. I have the values as Sales amount.  Here is the requirement.  I need to sort the Group level in descending order by the subtotal and the Years under each group in date order.  I cannot figure out how to mix the sorting to make this happen.  When I sort the Sales in descending order, the Years are out of sequence.  There is a picture of the original structure, the sorted by sales descending and what I actually need to get...

Any insights into making this happen is super helpful.

Thanks

Original matrix			Sort desc by Sales			Required sort
	Gross Sales			Gross Sales			Gross Sales
Group 1	600000		Group 2	750000		Group 2	750000
2020	100000		2022	400000		2020	200000
2021	300000		2020	200000		2021	150000
2022	200000	>>>	2021	150000	>>>	2022	400000
Group 2	750000		Group 1	600000		Group 1	600000
2020	200000		2021	300000		2020	100000
2021	150000		2022	200000		2021	300000
2022	400000		2020	100000		2022	200000
Group 3	500000		Group 3	500000		Group 3	500000
2020	125000		2022	250000		2020	125000
2021	125000		2020	125000		2021	125000
2022	250000		2021	125000		2022	250000

[SWhiteMKL]

I think I understand but when I add the Variable for CurrentGroup the way you suggest the column returns with a circular reference error.  Maybe this is not supported in DirectQuery Mode?

[Bipin-Lala]

Hi @SWhiteMKL,

There are limitations/ restrictions when using DirectQuery in Power BI, including limitations to DAX logic, but I am not sure if that is the case here. Could you please share a few more details about the error you are getting, including screenshots, maybe I would be able to help better.

Also check out the following blog to learn more about DAX and DirectQuery.

https://blog.crossjoin.co.uk/2017/05/09/dax-functions-directquery-and-unrestricted-measures/

[Bipin-Lala]

Hi @SWhiteMKL,

I was able to achieve the required sorting in your Matrix Visual, though it was not as forward as expected. This is because Matrix Visual in Power BI does not allow multiple sort columns as the table visual does. You would only be able to sort either by Sales values or by Groups/ Years at a time, as experimented and shown by you as well.

To achieve the required sorting shown below, follow the steps - 

• Create a calculated column in the sales table to calculate the Group Subtotal for each group. This will help us in sorting the group later. Let's call it Group Sum

Group Sum = 
VAR currentGroup = 'Matrix Sort'[Groups]
RETURN CALCULATE(SUM('Matrix Sort'[Sales]), FILTER('Matrix Sort', 'Matrix Sort'[Groups] = currentGroup))​

• You can see the Group SubTotals now using the calculated column. We want to sort the Groups based on this Group Sum. If you try that using the Sort By column functionality, it will throw a circular dependency error.

• To avoid this, create a duplicate Groups column that will actually be displayed in the matrix visual. Let's call it Visual Groups

Visual Groups = 'Matrix Sort'[Groups]

• Sort the Visual Groups column by Group Sum column.

• Create the matrix visual by adding the fields into current buckets as shown below

• Sort the Visual Groups Column in descending order by going into visual settings. Remember to select and sort only the Visual Groups field and not the years field, which should get sorted by default

Voila! It's done. Let me know if you face any issues!