Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sort a matrix column dynamically based on totals

I have a matrix where I want to sort both on the rows and on the columns at the same time. This would thus lead to the highest amount both for rows and columns to be in the top left corner of a Matrix. However, as of today I cannot sort the column by the amounts/measures. The column will be alphabetically ordered as long as I have both a column and a row value. 

 

As you see in the file, I want to use the Product Group as the column. This dimension needs to be sorted on the sum of the amounts. At the same time, the rows should also be sorted on the sum of the amounts which they already are. 

 

I have tried to create a RANKX formula which can be seen in the bottom matrix where I've properly ranked the Product Groups based on their sum of the amount. However, I cannot seem to get that to work in the matrix as I also do not want to display that as a measure. 

 

Also, a calculated column is not a solution to this as it needs to be dynamical. On top of this, we will need to be able to filter on different countries which might have different orders on the Product Group amount sum. As you can see, for 'NO' the order will be Large, Small, Medium. In the aggregated version, it should be Small, Large, Medium. 

 

The desired outcome is put as a picture to the right which I manually created in Excel. However, the desired outcome should also work when filtering on countries. 

 

I believe the solution would be to use a RANKX measure and somehow incorporate that in the matrix. How can I do that? 

 

Please also note that this needs to work on a much larger dataset, this is just a small example of what our real life problem looks like. 

 

PBIX file: https://drive.google.com/file/d/1F8rq5iF-nFYLlYHSydZYmG9KfVOFkJVm/view?usp=sharing 

6 REPLIES 6
amitchandak
Super User
Super User

@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

Anonymous
Not applicable

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:

Screenshot 2020-11-24 161638.png

Then in the matrix,put columns "__Product Group" and "Rank" in the columns field:

Screenshot 2020-11-24 161819.png

Finally you will see:

Screenshot 2020-11-24 161859.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

This error appears when I want to sort according to the rank.

bcastilloc_1-1657821910208.png

Anonymous
Not applicable

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,
Kelly

Did I answer your question? Mark my post as a solution!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.