Need to Keep Value as a Number when text label Attached

[kewaynes]

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?

[ray_aramburo]

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. 

[kewaynes]

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?

[ray_aramburo]

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

[kewaynes]

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...

[ray_aramburo]

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😅

[kewaynes · 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 "
)