topic Re: Can I sort matrix visual based off a specific column? in Developer

Can I sort matrix visual based off a specific column?

afaro — Fri, 27 Sep 2024 22:47:48 GMT

Let's say I have table like this:

Country	C1	C2	C3
Product	Sales	Sales	Sales
P1	10	20	50
P2	30	10	60
P3	50	0	10

Product is the rows in the matrix, Country is in the columns and Sales is the measure in the values.

I want to sort let's say sort by Sales ascending in country C2, so I would get something like this:

Country	C1	C2	C3
Product	Sales	Sales	Sales
P3	50	0	10
P2	30	10	60
P1	10	20	50

I want to do it by specific column like this.

Re: Can I sort matrix visual based off a specific column?

lbendlin — Fri, 27 Sep 2024 23:31:30 GMT

Yes, change the column total to only include C2.

Measure = if(ISINSCOPE('Table'[Country]),sum('Table'[Sales]),CALCULATE(sum('Table'[Sales]),'Table'[Country]="C2"))

Sort by the column total.

Hide the column total.

Re: Can I sort matrix visual based off a specific column?

afaro — Fri, 27 Sep 2024 23:27:32 GMT

I want to have the option to change by any country whenever I want instead of keeping static just for C2. So, if I want to do by C1 and C3, then end user should be able to do that.
To add, I don't want to create a measure where I remove the columns from matrix and explicitly add the country in the measure for example CALCULATE([Sales], Country = "C2") and so on. This is a solution but not something I am looking for since I prefer having to keep the columns.

Re: Can I sort matrix visual based off a specific column?

lbendlin — Fri, 27 Sep 2024 23:36:55 GMT

Sure, have a disconnected slicer for the country selection and in the measure read the current slicer value.

Re: Can I sort matrix visual based off a specific column?

afaro — Mon, 30 Sep 2024 20:14:09 GMT

The measure is no longer able to read slicer value using SELECTEDVALUE if it is disconnected.

Re: Can I sort matrix visual based off a specific column?

lbendlin — Mon, 30 Sep 2024 20:21:43 GMT

please elaborate?

Re: Can I sort matrix visual based off a specific column?

afaro — Mon, 30 Sep 2024 21:04:53 GMT

How did you change the column total to only include C2? I do not get that point.

What I tried to do was create a new measure just for sorting. This measure would calculate the value based on the value in the slicer and not the column value coming from the matrix. However, for this I had to disconnect the matrix from the slicer but when I did this, the new measure created for sorting no longer works since disconnection led to me not being able to read the value of the slicer using SELECTEDVALUE.

Re: Can I sort matrix visual based off a specific column?

lbendlin — Mon, 30 Sep 2024 22:19:31 GMT

I showed that in my measure, with ISINSCOPE.