Forum Discussion
How to custom sort column values in matrix visualisation
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 🙂
Anonymous , 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 nullIn 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
2 Replies
Anonymous , 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 nullIn 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-cThanks 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?