Format string in Tabular Editor in Thousands

[Anonymous]

Hi ,

Can someone help me to figure out the format string ot use for the below :

Current Format : 

$8,255,064

Desired Format : 

8,255 K

Thanks,

Deevs

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

It’s my pleasure to answer for you.

You may need to modify you date type in your model ,then try this in tabular editor:

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RobertSlattery]

That's what I assumed but can't get it to work in March 2021 version

When I add the K at the end, the thousands format no longer works and I get the actual value, not in thousands.

[v-janeyg-msft]

Hi, @RobertSlattery 

Try this: #,##0,.K.

Best Regards

Janey Guo

[RobertSlattery]

Hi Janey, thanks for the lightning response but, unfortunately, same result: 

1,000,000 -> 1,000,000 K

[v-janeyg-msft]

@RobertSlattery  #,##0,.K

[RobertSlattery]

Still the same.  In the service as well as desktop.  And Analise in Excel.

[RobertSlattery]

Apologies: it does work.  For some reason the visual doesn't refresh when I make the change. When I change the formatting in desktop, I need to refresh the visual in the performance analyser (I don't want to wait for the ables to refresh).  This is true if I do it in Tabular Editor or directly in the pbix.  When I use Tabular Editor to change the published version, I need to shift-refresh the browser (which I should have realised!).

Thanks!

[Anonymous]

@v-janeyg-msft ,

You are a Legend it did work :). Really appreciate your help.

Is there a document somewhere I can refer to figure out such formats?

Thanks,

Deevs

[v-janeyg-msft]

@Anonymous 

https://docs.microsoft.com/power-bi/create-reports/desktop-custom-format-strings

Best Regards

Janey Guo

[darentengmfs]

Hi @Anonymous 

Rather than changing it in Editor, you could do it at the visual level.

However, if you want to do it in Editor, you can try to do the following steps:

1. Create a new column, divide it by 1000
2. Round up/down your new column
3. Change the data type to text
4. Concatenate the new column with " K"

If you want to do it in DAX, use the create a new column using the following formula:

1. New Format = 'Table'[Value] /1000 & " K"

[Anonymous]

Hi @darentengmfs 

Thanks for that but i really don't want to create whole bunch of measures as I got 20 plus measures that I have to apply that.

I really need to figure out updating it in the Tabluar Editor.

Thanks,

Deevs