Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Sort by columns by value in Matrix Visual

I have a table contains ID and Week columns as below
IDWeek
001Week1
002Week2
003Week2
001Week3
003Week3
003Week3

And I use these 2 to create the Matrix Visual, and the value is Count of ID (I want to calculate how many times that each ID shows in different weeks)
The Matrix Visual is as below:

 Week1Week2Week3Total
0011 12
002 1 1
003 123

Now I can sort by Total column only.
My question is: If I want to sort by Week3, the visual should change to:

 Week1Week2Week3Total
003 123
0011 12
002 1 1
     
What should I do?

I already know that it's not possible to sort by value only.
But is there any way that I can get closer to the expected output? Like using ISINSCOPE function maybe?
Any answer will be helpful! Thanks!!

Need Help
Show and Tell
Tips and Tricks
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello Anonymous,

     

    This isn't directly supported through standard sorting options, but you can achieve the result by creating a custom measure and using it to sort the matrix.

    SortByWeek3 = 
CALCULATE(
    COUNT('YourTable'[ID]),
    FILTER(
        'YourTable',
        'YourTable'[Week] = "Week3"
    )
)

    Add this measure to your matrix visual (You can add it as a hidden column if you don't want it to be displayed) and sort by the SortByWeek3 measure.

     

    Hope this helps.

2 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Anonymous 
    Unfortunately, this functionality is not available with the core matrix, and I don't believe there are any workarounds to address this.
    Please vote on the idea about the issue (I voted):

    https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=1513f45d-a47a-4326-adbe-8bb0023da6c8

    But I free matrix in the marketplace with the needed sorting functionality :

     

    You need to get with the mouse cursor to the wanted column and give a right click to see the sort "menu".
    From here, the first click will sort the column in descending order and, the second ascending.

    The pbix with the example is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

     



     

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello Anonymous,

     

    This isn't directly supported through standard sorting options, but you can achieve the result by creating a custom measure and using it to sort the matrix.

    SortByWeek3 = 
CALCULATE(
    COUNT('YourTable'[ID]),
    FILTER(
        'YourTable',
        'YourTable'[Week] = "Week3"
    )
)

    Add this measure to your matrix visual (You can add it as a hidden column if you don't want it to be displayed) and sort by the SortByWeek3 measure.

     

    Hope this helps.