Forum Discussion
How to customise sorting range values in Matrix?
Need help with how to create a DAX measure to custom sort below in Matrix table:
The PO Range Value for "$5,001-$10,000" needs to be after "$1,001-$5,000" and '$50,001-$250,000" needs to be before "$250,001-$1,000,000"
The PO Range Value is created based on a DAX measure below:
Any assistance would be appreciated.
Create a sort order calculated column in the same table:
PO Range Sort = SWITCH ( TRUE (), 'Purchase Orders and Items'[Total Order Value] <= 1000, 1, 'Purchase Orders and Items'[Total Order Value] <= 5000, 2, 'Purchase Orders and Items'[Total Order Value] <= 10000, 3, 'Purchase Orders and Items'[Total Order Value] <= 25000, 4, 'Purchase Orders and Items'[Total Order Value] <= 50000, 5, 'Purchase Orders and Items'[Total Order Value] <= 250000, 6, 'Purchase Orders and Items'[Total Order Value] <= 1000000, 7, 8 )Then apply Sort by Column:
Select the PO Range Value column → Column tools → Sort by Column → PO Range Sort
7 Replies
Hi UrbanCK,
Issue is that it is considering PO range values as text and not as numeric values, to overcome this PO range column should be sorted by another column, Refer attached .pbix solution file, I've created a new column PO Range sort and then sorted PO range value column by PO Range sort column and getting the correct output in Matrix visual.
For sorting select PO range value column and then go to sort by column and select PO Range sort (refer screenshot below)
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Hi UrbanCK,
Please make sure you are creating a column and not a measure, PO range sort and Total Order Value are columns only. in the error it's indicating about the column "PO range Value 2", could you check if Total Order Value is dependent on it?
Kindly share the sample .pbix (remove confidential information or replace before sharing) if issue is not yet resolved after the fixes
Create a sort order calculated column in the same table:
PO Range Sort = SWITCH ( TRUE (), 'Purchase Orders and Items'[Total Order Value] <= 1000, 1, 'Purchase Orders and Items'[Total Order Value] <= 5000, 2, 'Purchase Orders and Items'[Total Order Value] <= 10000, 3, 'Purchase Orders and Items'[Total Order Value] <= 25000, 4, 'Purchase Orders and Items'[Total Order Value] <= 50000, 5, 'Purchase Orders and Items'[Total Order Value] <= 250000, 6, 'Purchase Orders and Items'[Total Order Value] <= 1000000, 7, 8 )Then apply Sort by Column:
Select the PO Range Value column → Column tools → Sort by Column → PO Range Sort
Hello UrbanCK ,
Here are the steps :
1. Create a new table using enter data .
ID Range 1 $0-$1000 2 $1001-$5000 3 $5001-$10000 4 $10001-$25000 2. Sort Range by ID ( select field Range, use toolbar ro sort by option, sort by ID)
3. Create relationship between this table and your original table using range.
4. Use Range from this table to your visual and you are done.
I hope this helps.
Did I answer your query ? Mark this as solution if this helps, kudos are appreciated.
Cheers.
Hi UrbanCK ,
Thank you for reaching out to Microsoft Fabric Community and Thanks to cengizhanarslan , divyed and grazitti_sapna for Sharing valuable insights.
Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.Best Regards,
Abdul Rafi
Hi UrbanCK ,
We wanted to check if your question has been resolved or if you are still facing any confusion feel free to reach out.Best Regards.
Abdul Rafi
