How to sort measure by another column

[Anonymous]

Hi Friends,

I have a visual which shows sales by customer. I have formatted the "Total Sales" measure to show currency units like K, M, Bn,

The measure is below.

Format Sales = IF([Total Sales]>99999999,FORMAT([Total Sales],"$#,0,,,.0bn"),IF([Total Sales]>99999,FORMAT([Total Sales],"$#,0,,.0M"),IF([Total Sales]>999,FORMAT([Total Sales],"$#,0,.0K"),FORMAT([Total Sales],"$#,,0"))))

The issue when i click on sorting for this measure in the visualization it is not sorting correctly (i know this will be treated as a text data type). Ideally Bn should come first, M second, and K last.

How can i make this work without loosing the currency formatting as per the measure criteria given.

@Greg_Deckler @rajendraongole1 @ChandeepChhabra @GilbertQ @dbeavon3 

[Ritaf1983]

Hi @Anonymous 
You can use dynamic string formating to keep the numeric value of your measure and the ability to sort it.

You need to switch on the feature from the options :

And modify the wanted format for the measure :

Video guide :
https://www.youtube.com/watch?v=s4jeX9EKON4

Pbix is also attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Anonymous · Accepted solution]

Hi @Ritaf1983,

It did work for me.

Correct me one thing, is the dynamic measure formatting is not suported in direct query mode?

In import mode it worked.

[Ritaf1983]

Hi @Anonymous 

I must point out that DirectQuery is my weak spot, as I know it only from documentation and not from personal experience. However, I am familiar with this note regarding the topic:

**DirectQuery over AS**: When you click "Make changes to this model" on a live connected report, this shifts the connection to DirectQuery over AS. Generally, this allows you to make changes to the format strings of the remote model measures. For the public preview of dynamic format strings for measures:
Remote model measures with dynamic format strings defined will be blocked from making changes to the format string, whether to a static format string or a different dynamic format string DAX expression. Remote model measures cannot be changed from a static format string to a dynamic format string DAX expression defined in the local model. Local model measures will also be blocked from using dynamic format strings for measures. These restrictions are being explored and may change in future releases of Power BI Desktop.

I would still try, and if it doesn’t work, I don’t think there’s a solution for the sorting issue.

Additionally, when I looked at your question again, I noticed another potential issue regarding table readability. When you apply different formats based on the size of numbers, the reader's brain might get confused. A number that looks shorter could actually be larger in value. For example:

- 1M
- 100K

Even though **100K** has more characters, **1M** is actually larger. So it might be worth splitting the data into several groups based on their size—like large, medium, and small—using three separate smaller tables. This way, you avoid the confusion and can place the units in the header instead of repeating them in every cell and solve the technical issue because you will have only numbers.
Like sales K$
100
  50
Sales in Bn
8
2
etc...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Anonymous]

Hi @Ritaf1983 ,

Thanks for answers. It seems dynamic formatting wont work with direct query mode.

[Ritaf1983]

Hi @Anonymous 

So unfortunately, I doubt there’s an elegant way to handle this 😞 It’s possible to add a ranking column to the table, but it’s still an additional column... And every time a user tries to sort by the actual amounts, the sorting will break.

i attached a pbix with this option just to be on the safe side 🙂

And it’s really, really problematic in that same visualization to mix formats. I did an experiment like this in one of the meetups I led on data visualization, and people either got confused or took time to understand who’s bigger than whom in such cases. So maybe in this specific case, it’s more of a feature than a bug 🙂
P.s you marked your reaction to me as a solution is this what you meant or it is a mistake?

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Anonymous]

Hi @Ritaf1983 ,

Thanks a lot for your effort.

yes creating a rank column will not work since the users always want to sort the sales measure column only.

Seems like no way we can do it.