Sorting Text "Currency" measure by numeric value

[Danch89]

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

[SpartaBI · Accepted solution]

@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 🙂