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

Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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. 

      • v-kelly-msft's avatar
        v-kelly-msft
        Community 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!