Forum Discussion
Sort a matrix column dynamically based on totals
Anonymous , if try sort a column based on total (measure) . But we can sort Small , Large And medium.
You create a sort column and mark that as sort column for Product Group .
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
And how would I create a sorting column that would dynamically change depending on the filters applied? I cannot just sort Small, Large and Medium statically. As said in the example, the order will change depending on when I filter on countries, so following your link will not help in this case.
Hi Anonymous ，
Create the following columns:
in Purchase table:
Total sum = SUMX(FILTER('Purchase','Purchase'[Product ID]=EARLIER(Purchase[Product ID])),'Purchase'[Amount])In product table:
Value = LOOKUPVALUE('Purchase'[Total sum],'Purchase'[Product ID],'Product'[Product ID],0)Rank = RANKX(GROUPBY('Product','Product'[Product Group]),SUMX(FILTER('Product','Product'[Product Group]=EARLIER('Product'[Product Group])),'Product'[Value]),,DESC,Dense)_Product Group = 'Product'[Product Group]And select column "_Product Group">sort by Rank:
Then in the matrix,put columns "__Product Group" and "Rank" in the columns field:
Finally you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
It doesn't work dynamically, e.g. when I change filter to Country = 'NO', then it shows "Small" as the largest, even though it isn't for NO specifically. Small is the largest on an aggregated level, but it needs to change the rank and order depending on the different filters that can be used.
Hi Anonymous ,
Yes,dynamically isnt available.It is a workaround to rank both row and column,adding a rank order in the Columns field to make it rank as needed,we cant use measure,that is why the rank cant be dynamic.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
This error appears when I want to sort according to the rank.
