Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
kewaynes
Helper III
Helper III

Need to Keep Value as a Number when text label Attached

Hi,

 

I have numbers than I need to add description labels to. For example, the yield for a farm field. I need to have the number followed by a 't' to indicate tons.

i.e. - 8,457 t

 

This is easy enough using an '&'. However, doing so turns the measure value into a text string, which causes the number portion to lose it's for matting. 

i.e. - 8457 t

 

This can of course be overcome by using the FORMAT function. However, I also need these numbers to change to different countries' number formats. 

i.e. - for German users, it would be 8.457 t

 

The approach I outlined about doesn't seem to work. I believe this is because Power BI now simply views the measure as a string and doesn't do the automatic changing of number formats when someone from Germany views the report. 

 

Does anyone know a way around this?

 

1 ACCEPTED SOLUTION

Agreed. I got this to work. But it would obviously need to have 9 more substitutes added in order to account for all decimal number possibilities. 

 

//first substitute replaces '.' in the decimal seperator with a ','. Need to have a trailing number and space so DAX can differentiate the decimal seperator location from the thousand seperator location
SUBSTITUTE(
//second substitute converts replaces the ',' in the thousand seperate with a '.'
SUBSTITUTE(
FORMAT( [Total Yield (Dry)_2], "#,0.0" ) & " " & [Tons Label],
",", "."
),
".6 ", ",6 "
)

View solution in original post

6 REPLIES 6
ray_aramburo
Super User
Super User

If you will be using the measure for aggregation purposes, there's no work around since any number to be added needs to be numeric data type.

For these cases it is usually recommended to create "read-only" aggregated tables (either with DAX or Power Query) where you can customize the format as desired. 





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Thanks for the quick reply, Ray. I did try and set up some DAX logic where I attempted to have the number format portion of the measure be determined based on the USERCULTURE.

 

However, I couldn't get a number format to work for Germany. I tried something like the below:

 

FORMAT(number_value, "#.0,0")

 

But it didn't format correctly. It just had the '.' in the decimal seperator and therefore nothing in the thousand seperator.

Any idea what I could be using to format as a German number format?

I would do that portion in Power Query with a conditional clause and leverage the "Column from examples feature" as probably you would need like a 3+ step transformation process (converting text, splitting number, adding delimiters, concatenating them again) to do it in one go





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Gotcha. So, if I did need the numbers to be dynamic, it sounds like that's not really possible atm. My only option would be to drop the labels from the end of the measures so that Power BI recognizes them as numbers again...

It is always possible with a bunch of workarounds and logic but yeah, sometimes it's simpler convincing the customer/stakeholder that the effort for aesthetics isn't just worth it😅





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Agreed. I got this to work. But it would obviously need to have 9 more substitutes added in order to account for all decimal number possibilities. 

 

//first substitute replaces '.' in the decimal seperator with a ','. Need to have a trailing number and space so DAX can differentiate the decimal seperator location from the thousand seperator location
SUBSTITUTE(
//second substitute converts replaces the ',' in the thousand seperate with a '.'
SUBSTITUTE(
FORMAT( [Total Yield (Dry)_2], "#,0.0" ) & " " & [Tons Label],
",", "."
),
".6 ", ",6 "
)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.