Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 Matri...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

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

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

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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. 

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!