topic Re: Sort matrix in Custom Visuals Development Discussion

Sort matrix

Daniel1993 — Fri, 08 Mar 2024 08:29:42 GMT

Hi all, hope someone can help me with below.

I have a matrix with two rows: risks and departments.

I would like to sort the average column (as what you see below), but would like to keep the order of first column with: 1. Risk, 2. Risk and 3. Risk. Average column & trend are measures.

Incorrect:

Risks	Average	Trend
2. Risk
a Department 1	65.4	1.3
b Department 2	71.8	10.5
1. Risk
a Department 1	57.5	-0.1
b Department 2	63.5	1.5
c Department 5	64.3	5.3
d Department 6	71.4	3.4
e Department 3	77.0	0.6
f Department 4	87.6	-0.5
3. Risk
a Department 1	78.3	1.9
b Department 3	81.3	5.5
c Department 4	82.8	2.2
d Department 2	88.0	1.7

It should be like:

Risks	Average	Trend
1. Risk
a Department 1	57.5	-0.1
b Department 2	63.5	1.5
c Department 5	64.3	5.3
d Department 6	71.4	3.4
e Department 3	77.0	0.6
f Department 4	87.6	-0.5
2. Risk
a Department 1	65.4	1.3
b Department 2	71.8	10.5
3. Risk
a Department 1	78.3	1.9
b Department 3	81.3	5.5
c Department 4	82.8	2.2
d Department 2	88.0	1.7

Hope someone can help me! Have a nice day 🙂

Re: Sort matrix

qqqqqwwwweeerrr — Fri, 08 Mar 2024 09:06:43 GMT

Hi @Daniel1993

You try by creating a temp table by taking unique value from risk column.
I have used sample data where i have once column call family.

and temp table is

by specifiying the other in which you want to sort the column.

Now in you table create a new columns: using this:

Column = RELATED('Table'[Index])

Now once you create the matrix visual
step 1 click on Risk column
step 2 click on Sort by colum
Step 3 as per the aces or desc it will be sortet
See below visual
initallly i had family_1 at first place but after sort it looks like this

you can also follow this video for custom sort: https://youtu.be/PUaFtLpIMvY?si=n4pkXmw984_LSByi

Did I answer your question? if not please more details about data and problem statment
Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards

Re: Sort matrix

Daniel1993 — Fri, 08 Mar 2024 13:06:19 GMT

Hey! Thanks for explaining, but I think I was not clear.

Now I can sort on ''Risks'' to see below.

Risks	Average
1. Riskname
<NAME>	74.6
<NAME>	61.1
<NAME>	82.4
<NAME>	62.2
<NAME>	61.6
<NAME>	61.4
2. Riskname
<NAME>	60.8
<NAME>	68.1
3. Riskname
<NAME>	84.7
<NAME>	76.2
<NAME>	81.3
<NAME>	73.6

And I can sort on Average, and below is what I got:

Risks	Average
2. Riskname
<NAME>	60.8
<NAME>	68.1
1. Riskname
<NAME>	61.1
<NAME>	61.4
<NAME>	61.6
<NAME>	62.2
<NAME>	74.6
<NAME>	82.4
3. Riskname
<NAME>	73.6
<NAME>	76.2
<NAME>	81.3
<NAME>	84.7

Now, the problem is that I would like to sort on ''Average'' from low to high, but the risks should be in order of 1-2-3. The outcome should be like this:

Risks	Average
1. Riskname
<NAME>	61.1
<NAME>	61.4
<NAME>	61.6
<NAME>	62.2
<NAME>	74.6
<NAME>	82.4
2. Riskname
<NAME>	60.8
<NAME>	68.1
3. Riskname
<NAME>	73.6
<NAME>	76.2
<NAME>	81.3
<NAME>	84.7

I tried your solution, but it seems like it doesn't work 😞 Or should it work?

Re: Sort matrix

qqqqqwwwweeerrr — Fri, 08 Mar 2024 14:31:19 GMT

Hi @Daniel1993

I tried of the post on commnuinity it worked for me, but for that you may need to convert your matrix to table view which i am not sure acceptable to you. Here is link for the solution on commnunity: https://community.fabric.microsoft.com/t5/Desktop/Order-multiple-columns-in-a-matrix/m-p/135815

It worked for me here is the Schreen shot:

If it works for please accpt the solution, other will be benifited by this

Regards

Re: Sort matrix

Daniel1993 — Fri, 08 Mar 2024 16:07:35 GMT

Thats unfortunately not possible for us, we need the matrix, because I have 2 different rows..

one with:

1. Risk

2. Risk

3. Risk

And within each risk, there are different risknames (other column)..

If this is not possible, than I have to deal with it haha.
I don't want to have multiple names in the table.