cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 

5 REPLIES 5
Super User IV
Super User IV

@matsL1730 , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 @matsL1730 ,

 

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!

 

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 @matsL1730 ,

 

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors