Applying different number format for the one measure if used in chart or a table visual

[bryanWA]

My source system records data at the $000 level, (so 5,000 equates to 5 million) which I have been multiplying by 1,000 in my SQL to make dollars so that if I use that measure etc in a chart the auto formatting shows the right magnitude.  

This fixes the chart display, but then internally we use tables a lot so the dollar format can be confusing for others, as it is not the same as the source system.  Damned if you do and damned if you don't.

As an intermin if have created a few new measures like "Total Exp Data = [Total Exp]" and then formated the new measure with #,###,, which works but means I am duplicating the number of measures that are used in both a chart and a table/matrix.

I have seen the dynamic formatting but in this instance I want to the format to be #,###,###  if the measure is used in a chart (so auto formatting will show the right magnitutde) but be #,###,, if used in a table.  This will also free up some additonal real estate in the tables, as the lowest number that can be entered into the source system will be 1 (equateing to 1,000) so there are lots of zeroes across the page. 

I could turn off the Auto Display Units in the chart formatting, but we deal with data that can range from the single thousand to the tens of billions, so the Auto Display Units is handy that that regard.  

Any suggestions would be greatly appreciated.

[bryanWA]

Thanks.  Worked a Treat for Table and Matrix visual with a single set of values, but if  there mutiple measures as values the Dynamic formatting is ignored.   You have to use the Format Visual - General - Data Format to choose Custom or the underlying number is shown. 

[danextian · Accepted solution]

Hi @bryanWA 

You can use a disconnected table that has the format strings and apply them to the measure. You can specify in the format string formula the default formatting. In "#,##0,", the last comma divides the value by a thousand. Another comma will divide it by a million. You will need to use a slicer, visual or page level filter to be able to apply the  format string outside the default.

Here's the M code for the format strings.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsnPyUksUtJRUlHWUQZShkqxOtFKAfmleSnFQO6hxRBhI7CwX2luUipIMUTQGCwYkpFfWpwIUQ4UVzbQATJMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Format = _t, Sort = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Format", type text}, {"Sort", Int64.Type}})
in
    #"Changed Type"

Unfortunately adding K or M to the format string breaks the formatting.