Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
PBIX file can be found here :
I have two visuals in the file:
(1) is the total profit by Company and this is sorted in Descending Total Profit
I was wondering if anyone knew of a way to sort (2) so that the Columns Totals are also sorted in Descending Total Profit to match the Company Order in the column visual
If someone could explain how the matrix in (2) could be configured to show the Columns in the sequence of
Company B then Company A then Company C from the left to right (i.e. Descending Numerical Order of Total Profit)
that would be great.
Thanks,
Richard
Solved! Go to Solution.
Hmmm. Interesting. I haven't tested this, but here's what I'm thinking. You know how Power BI default sort is alphabetically, which presents a problem with Month Names, so in our date table we create a sort column. I'm wondering if you can create a calculated column using RANKX to rank your Companies by Total Profit. Then in your company table, sort your company name by the rank column. Then in your visual, sort by Company ASC. Give that a go and let us know if it works.
**edit: on further thought, I don't think an X function will work as a calculated column. You may need to create a calculated table with SUMMARIZECOLUMNS and RANKX as your expression. Use this output as a dimension of your company table. Then use the company name value from this calculated table as your axis value in your visual.
Hi @chonchar - thanks for taking the time to respond but the functionality you are describing sorts the vertical columns in vertical ascending/descending order and doesn't solve the issue described above.
"if someone could explain how the matrix in (2) could be configured to show the Columns in the sequence of
Company B then Company A then Company C from the left to right (i.e. Descending Numerical Order of Total Profit)
that would be great. "
Thanks
Hmmm. Interesting. I haven't tested this, but here's what I'm thinking. You know how Power BI default sort is alphabetically, which presents a problem with Month Names, so in our date table we create a sort column. I'm wondering if you can create a calculated column using RANKX to rank your Companies by Total Profit. Then in your company table, sort your company name by the rank column. Then in your visual, sort by Company ASC. Give that a go and let us know if it works.
**edit: on further thought, I don't think an X function will work as a calculated column. You may need to create a calculated table with SUMMARIZECOLUMNS and RANKX as your expression. Use this output as a dimension of your company table. Then use the company name value from this calculated table as your axis value in your visual.
Great idea @CoreyP which worked perfectly.
Although I did the rank in PowerQuery via an index I'm confident the same approach would work within a calculated column also (using SUMMARIZECOLUMNS and RANKX).
Updated pbix can be found here: https://www.dropbox.com/scl/fi/syn5vuamab9qovj96lpw0/Question_About_Matrix_Sort_Order.pbix?rlkey=1pl...
The Company Sort Sequence (Greatest to Smallest profit) in the matrix above now matches the order in the graph.
Nice work and thanks for taking the time to respond.
Richard
Hello,
I found this post: https://community.fabric.microsoft.com/t5/Desktop/Matrix-Sort-Column/m-p/2189220#M802089
and this one: https://community.fabric.microsoft.com/t5/Desktop/Horizontal-Sorting-of-tables/m-p/353674
I didn't know this, but as of two years ago you can't sore horizontally. They may have fixed this issue, but other than manually moving them, I don't think it can be done. I'll keep looking for more current documenation on this matter and reach out if I find something.
Additionally, I hope I am understanding your problem correctly.
Best of luck,
Cam
@chonchar I suspect you are correct and that there isn't a way to do this at present but wondered if anyone had found a non conventional work around/solution.
Appreciate you sending the link to the ideas page
Thanks
Richard
Hi,
I'll try my best:
Open the filters pane with the visualization selected. On the top of that pane where the visualizaton is selected, you can click the three dots and then sort Asc/desc from there.
Hope that helps,
Cam
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
74 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |