How to show the text in the format I want?

[raymondxie]

I have a calculated column to show the total:

Total Records = FORMAT(COUNT(MasterFinal[ID]),"#,###")  

This is NOT what I want, instead I want to show it as: 55,670, and I also want to do some styling on it, can anyone tell me how?

Thank you very much.

[Greg_Deckler]

Click your visual, expand the "Visualizations" pane if not already. Click on the paint roller icon. Expand "Data lable". Change the Display... to something other than "Auto", like "None". Style to your heart's content. (well, probably not, but you can do a lot of styling changes there).

[raymondxie]

Thank you @Greg_Deckler, doing kind of tricks there but not enough, it only shows: 55669 now, what I want in this case is 55,669, since the column is created with: Total Records = FORMAT(COUNT(MasterFinal[ID]),"#,###")  , why it doesn't stick to the format "#,###"?

How can I force it to show the format I indicated already in the formula?

Thanks.

[Vvelarde]

hi @raymondxie

select your column and go to modeling 

[raymondxie]

@Vvelarde Thanks. Nothing happened after I followed your instruction

 

[Greg_Deckler]

Hmm, is that a column or a measure, I assume that Total Records is a column. So, here is what I think might solve the issue. Try creating a measure that does your Count of Total Records. Format it as a Whole Number and click on the little "comma" icon for that measure to format it. Then, use that measure in a card visualization and see if that solves it.

[raymondxie]

Thank you @Greg_Deckler

Creating Measure resolves the issue, but, is there a more flexible customization? comma on thousand doesn't apply to many country and area.

[raymondxie]

just spot another serious issue here:

This is the measure:

Total = FORMAT(COUNT(MasterFinal[ID]),"#,####")  

It shows as 54,000 in the report, but this is not correct, it should be 55669, if you look at my previous post, you will see I did see it showing 55669 or 55.67k

What am I missing here? why the number is wrong? Thanks.

[Greg_Deckler]

I'd need some sample data probably to resolve that one, not sure why that is the case, I didn't seem like you were having that issue in your earlier posts. Honestly, I would get rid of the FORMAT component of your measure, you shouldn't need that at all.

[raymondxie]

Thank you @Greg_Deckler

After troubleshooting from end to end, it turns out there is some other issue in the data side.

[v-ljerr-msft]

Hi @raymondxie,

Great to hear the problem got resolved. Could you accept the corresponding reply as solution to help others who has similar issue easily find the answer and close this thread?

Regards

[raymondxie]

@v-ljerr-msft

Not really, it seems there is no custom formatting, what about if there is a requirement to show the number as 1,2345,5678 instead of 12,345,678?

Thanks.

[Sean]

@raymondxie How about this?

Total Records = FORMAT ( COUNT ( MasterFinal[ID] ),"#,0" )  

[raymondxie]

@Sean

What I said is to display it as 1234,5678

This is actually common format in Asia

Thanks.

[Sean]

@raymondxie Okay I misread the question. Now I see what you mean you don't want a "thousands" separator!

According to this

"In China, comma and space are used to mark digit groups, because dot is used as decimal mark. There is no universal convention on digit grouping, so both thousands grouping and no digit grouping can be found. Japan and Taiwan are similar; although when grouping by myriads, kanji or characters are frequently used as separators: 1億2345万6789 / 1億2345萬6789. Commas are used when grouping by thousands."

So I think the FORMAT function actually gives you what its meant to do even in your case...

But you should maybe contact them if this is not available under your Regional setting!

[raymondxie]

@Sean

Thank you for finally got what I meant and sorry for not being more clearer on it, there must be something missing in my formula? as it really is not working as expected, here is the format and screenshot:

any thought? How to display it as 2,9780?

This is not a must-do work, I am more concerning on how the tool (Power BI) can give designer the flexibility to cope with different needs and so far my evaluation isn't really as good as other products, customized formatting is a key issue here: for example conditional formatting in Grid doesn't seem to be out of box.

I am still learning so I could be wrong.

[raymondxie · Accepted solution]

ok, for this particular issue, I figured it out:

So the FORMAT function does allow more customization flexibility.