How to custom sort column values in matrix visualisation

[ac_95]

Hello,

I need some guidance on how I can tackle below scenario. The below screenshot is an example of what I am trying to solve. 

The first part of the screenshot indicates the input I have, having columns as: Region , Selection and Value. The second part of the screenshot is how I want the matrix visualisation to look in Power BI.

My current problem area is, I am unable to custom sort 'Selection' column values in the matrix visualisation. In the matrix visualisation, I have Region and Selection in the Row section. The way I need it to display is exactly how I have attached in the screenshot, first Tax, then Fine and then Gross%

I tried doing creating a calculated column where each selection type will be numbered as 1,2,3. However, I am unsure on how I can use this calculated column in the matrix visualisation without it being displayed there.

I would really appreciate any help/guidance on this.

Thank you 🙂

[amitchandak · Accepted solution]

@ac_95 , if the selection is a column you need to have a sort column, probably create that in the power query .

mark selection order as sort column of selection 1 in column tools

selection Order = if [Selection] = "Tax" then 1
else if [Selection] = "Fine" then 2
else if [Selection] = "Gross %" then 3
else null

In DAX to avoid circular dependency have columns like , mark selection order as sort column of selection 1

selection 1 = 'demo'[Selection]

selection Order =
SWITCH(
'demo'[Selection],
"Tax", 1,
"Fine", 2,
"Gross %", 3,
BLANK() -- This will return a blank if the value does not match any of the above
)

In case they are from a calculation group or field parameter, you can control order there or have a sort column like the above

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

[ac_95]

Thanks @amitchandak for the solution there.

This does help me sort the Selection column. However, when I put that selection column under Row section of matrix visualisation it still looks unsorted. It does not come up in a sequence in the matrix visualisation as Tax, Fine and Gross% on respective rows of the visual.

How can I achieve that?