cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StuBee
Helper I
Helper I

Switch Table Custom Format failing to sort data correctly

Hello,
 
When I use a custom format on my data, the data gets sorted based on the first number as shown below.

 

StuBee_0-1663419108946.png

 

This is the code I am using, I have tried with max instead of values and without stating that it can't be 0.

 

Also have used the "Currency" option, however this put all the negative numbers at the top of the list as they where in ().

 
Sales = if(HASONEVALUE('Table'[Table]),
Switch(
True(),
Values('Table'[Table]) =
"Revenue" && [Rev LW]<>0, FORMAT ([Rev LW], "$#,###"),
Values('Table'[Rev/Units]) =
"Units" && [Units LW]<>0, FORMAT([Units LW], "0")),
[Units LW])
 
Can anyone give me any ideas on how best to tackle this,
 
Thank you
1 ACCEPTED SOLUTION
daXtreme
Super User
Super User

Hi @StuBee 

 

It's a bad idea, indeed, to format a numeric measure with FORMAT and return a string (unless you need to incorporate such a value into some kind of text but even then you create a version of the measure). Such "values" will always be sorted as strings, not numbers. That's one thing. A measure should always return the raw value (always of the same type). Mixing floats with integers is a NO-NO. Measures should be consistent. Otherwise you'll be facing issues. And, as we see, you do. Formatting a measure should be done in the UI option in the ribbon. So, if you have a measure that wants to return different types of info, you're most likely doing it wrong. You should rethink the design.

View solution in original post

2 REPLIES 2
daXtreme
Super User
Super User

Hi @StuBee 

 

It's a bad idea, indeed, to format a numeric measure with FORMAT and return a string (unless you need to incorporate such a value into some kind of text but even then you create a version of the measure). Such "values" will always be sorted as strings, not numbers. That's one thing. A measure should always return the raw value (always of the same type). Mixing floats with integers is a NO-NO. Measures should be consistent. Otherwise you'll be facing issues. And, as we see, you do. Formatting a measure should be done in the UI option in the ribbon. So, if you have a measure that wants to return different types of info, you're most likely doing it wrong. You should rethink the design.

Ahh ok, so is there no good way to use a switch function to return a whole number and switch to currency?

 

That's fine, they can see it is revenue from the box they click can't they.

 

Thanks, you confirmed what I was hoping wasn't the case.

 

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors