Numeric Sorting when Using Format Function in Switch

[Hans_Adams]

Hello,

I have a use case that would need a matrix to display Revenue, Units and ASP.

I'm using a switch statement to navigate between these 3 metrics, however they require seperate formatting:

 Revenue: 725,253K

 Units: 725,253,443

 ASP:   43.12

example code:

I use the matrix visual to format the Revenue and the format function to format ASP and Units.

And all is well, untill someone tries to sort the matrix 😞

Only for the revenue the sort is working as an integer both ASP and Units are sorted as a text string.

Reading up in the comunity learned me that this is expected behavior as the format function always converts to string. 

Does anyone have a work around for this?

I already tried to create a custom sort (e.g. highest running matrial is nr 1 and so on), hwoever this doesn't work as trhe report I'm building serves a global company and in China the highest running material wouldn't neccessary be the same as in America --> so if I filter America data a low runner there could end up high.

Many thanks in advance for sharing your experience

Let me know  if I need to explain it a bit more detailed.

Grts

[amitchandak]

@Hans_Adams , Please check if the field parameter can serve the purpose, their base measure formatting will work

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE

[Hans_Adams]

Currently my Matrix is populated like this:

Rows: Product segment; Product group; SKU (so a basic Product Hierarchy)

Columns: -nothing-

Values: FY20,FY21, FY22,Var FY22 vs FY21, Var FY21 vs FY20

Would it be possible to use parameters to change all these value columns? 

Or would I need to change the mechanic of the matrix?

e.g. 

Rows: Product segment; Product group; SKU

Columns: Fiscal Year

Values: _metricParameter

and exclude de variance columns