Forum Discussion
Sorting Text "Currency" measure by numeric value
Hi,
I have a measure "Sales" - which switches between showing US sales UK or GBP or USD. I'm formatting the measure to include the correct currency as per below screenshot.
When I add this measure to a table and sort it, it doesn't sort per the numeric value and instead sorts as text - (because the measure is being recognised as text from the formatting). Example below.
Any thoughts how I can get around this so when sorting in a table it's sorted numerically whilst keeping the currency marker?
Thanks
Dan
Danch89 once you use the FORMAT function than you get a text and that's it 🙂
The solution for your use case is to use calculation groups (you need external tool like tabular editor for that).
You will be able to write a dynamic format string, but the value of the measure will remain a currency and you could sort it as you intended.
This is the best source to learn calculation groups (and DAX in general):
https://www.sqlbi.com/calculation-groups/
In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂
1 Reply
- SpartaBI
Community Champion
Danch89 once you use the FORMAT function than you get a text and that's it 🙂
The solution for your use case is to use calculation groups (you need external tool like tabular editor for that).
You will be able to write a dynamic format string, but the value of the measure will remain a currency and you could sort it as you intended.
This is the best source to learn calculation groups (and DAX in general):
https://www.sqlbi.com/calculation-groups/
In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂