I spend the whole morning trying to understand the logic behind the custom formatting feature. The official documentation just confused me more. Thank you so much for the video. This definitely saved my afternoon.

A clear and comprehensive explanation. Thank you!

The best video I have seen explaining the types of format. Brilliant!

@HowtoPowerBI

2 жыл бұрын

Wow, thanks Isidre! Very happy you liked it!

Fantastic! I've been waiting for this for quite a while! Thanks a lot Bas

@HowtoPowerBI

3 жыл бұрын

Glad you like it! Thanks for watching Pedro 😀

Nicely explained.Tricky ... Soon for 200 K (#,###, K) subscribers.Good Luck....

Great content! Helped me solve the issue. Thank you for this informative video !!!

Awesome video, just what i was looking for, ty very much!

@HowtoPowerBI

3 жыл бұрын

thanks for watching Juan, happy to hear it helped!🙂

Super clear and well paced Video 👍

Hi Bas! Thanks for the great contect. You helped me a lot again. I couldn't find info about these formatting details anywhere. But always can rely on your channel. Thank you!

@HowtoPowerBI

2 жыл бұрын

awesome, I'm very happy to hear it helped a bit! 😎👊

King of Power BI Bas! Dude I love your Videos. They make Power BI Look so easy.

@HowtoPowerBI

3 жыл бұрын

thank you so much, trying to keep it simple 😀

Brilliant....despite coming to be comfortable writing DAX I never fully understood how the custom formats worked and more often than not just stuck to the formats I knew. Thanks so much for the great content! I only discovered your channel last week but have already picked up so many new tips that I’ve never seen elsewhere!

@HowtoPowerBI

2 жыл бұрын

awesome, thx for your sub Peter! 👊😀

Lifesaver! Thank you!

Awesome...so helpful!

thank you for a good video with very useful practical information!

@HowtoPowerBI

3 жыл бұрын

Glad it was helpful! 😃

you are a star mate!! please keep your amazing videos coming :)

@HowtoPowerBI

Жыл бұрын

Will do Varun, thx!

you aare the best man !

Great and easy explanation! Thank you.

@HowtoPowerBI

4 ай бұрын

😊 🙏

So good, thanks !!

Hi thank you for the content. this content help me a lot.

@HowtoPowerBI

Жыл бұрын

that is great to hear! thanks Daniel! 😀

Thank you very much

Hello, thanks for the video. Can you explain how to get two different format for one value. For eg- I want the total sum to be indicated as K if the value is in Thousands and M if in millions in same chart. Tried using semi colon, but not working. please help.

useful info on custom formatting in power bi

@HowtoPowerBI

3 жыл бұрын

Glad to hear that, thanks! 🙂

Very helpful explanation how to use custom formatting

@HowtoPowerBI

2 жыл бұрын

Glad you think so! thx for watching Emmaus! 😀

Thanks you are a genius. love your videos

@HowtoPowerBI

2 жыл бұрын

So nice to hear, thx Kabir!!! 😊

@kabirhussain1725

2 жыл бұрын

@@HowtoPowerBI Bas, i have Q: i want make a measure in which i want show previous month, but not sales. I want show month previous month on a card visual

Very useful video

@HowtoPowerBI

3 жыл бұрын

Thanks a lot, great to hear! 😀

How can i format my number with a comma separator as per indian stadards or us standards for example: 5,00,000 (what would be the custom format for this?) and vice versa, how can i display as per 500,000 Any help would be appreciated.

Hi Bas. I have a question, perhaps you can help. If I understood correctly, is the order in the custom format line: positive figures ; negative figures ; zero. Right? I have the problem with very very small differences in a field. So It shows sometime things like "E9 - 13". So I want to see a 0. So I used your hint with the custom format and put in the following as a test "0;0;0" and it works for the zeros. No scientific format. But of course I miss the thousand comma. So to make it correct I used "#,###;-#,###;0" and this works fine for positive and negative figures. But now the zero disappears?! :( I have tried so many possibilities, but it doesn't work. Can you help? Thanks in advance and greeting from Germany

Hi Bas, Your Power BI videos are so informative and explained very well...I have gotten so much out of this...Thanks for doing this great service! - naga

@HowtoPowerBI

8 ай бұрын

Hi Naga! Thanks a lot for your message and supporting the channel, much appreciated:)

Hi Bas thank you for this great content. however I'm having an issue as by defaukt these string formats are European formats. to work with American string formats they replace "," by ".", so when you see 1,284 M, you should see 1.284 M. is this something that I can sort with Powerbidesktop or something that due to my laptop configurations? any idea on how can change this to use the "." instead of ","? thank you

Hi Bas, thank you for this video and your other rich content! Just wondering, is it possible to use this approach to show percentages and whole numbers in one column?

@thesunnynight

2 жыл бұрын

Of you want to show them at the same time no,of you want to switch between them,you can do with calc group (formatting expression)

@siyandamagubane8711

2 жыл бұрын

@@thesunnynight Thank you Bas - do you have a video on how to do this? Please share link? Otherwise, please consider my unique case below: We have a company that is operating in 2 countries and if we have identified specific targets for each country to achieve in 2021. The targets are not the same because the scale of our operations in not the same. For example, in country A we have a target to get 500 new customers while in country B we just want them to have 300 new customers. We also have different targets on improved customer satisfaction, for example in country A we want customer satisfaction to improve to 90% while in country B we want the satisfaction to improve to 95%. As you can see, in our targets column we have whole numbers and percentage numbers - and the same will apply to our reach target. When I upload my excel sheet into power bi, the percentages convert to decimal numbers - and for reasons you know, I cannot change the number formatting in power query to percentages because it converts all the numbers, including whole numbers. On my visuals, the whole numbers appear correctly, but the percentage numbers appear as decimals, e.g. 20% is represented as 0.2. I have been stuck on this for the past 3 weeks now - and I am very desperate for any support. In this case, I would appreciate it you could share the formatting expression I can use to address my challenge. Please note, I am new in power bi - and everything I know so far has been self-learned through your videos and many other channels here on KZread. So far I have been very successful, including having power bi as our primary reporting platform to our board of directors! Please help as best as you can - thanking you in advance!

@thesunnynight

2 жыл бұрын

@@siyandamagubane8711 if i understood correctly, your problem Is formatting a value as percentage. You do not need to do that in Power query,you can do that in the formatting measure option or - if you are using implicit measure (deprecate!) - you can Simply adjust the format field or data label option of the visual object

Hey great video! I've been looking for exactly this content and you've summarised it so well! Is there a custom formatting option to enable the excel time "[h]:mm:ss" formatting in PowerBI?

@HowtoPowerBI

Ай бұрын

h:nn:ss

Is there an option to add like a button with which you can simply switch between two or more formats?

Hi Bas, thank you for the video. I am wondering if you can help. I have measures being used in multiple tables and visuals. In most tables the best format for my data is shown in #,##0,;(#,##0,);"-" so it is in thousands. However there are some visuals shown in millions and I would like to have one decimal place for those. When I change the value format in those visuals, it show 0 with 0 decimal, and 0.4","M with one decimal. Is there anyway that I could get rid of the "," except duplicating all my measures with a different number formating? If create the same measure with different number formating that means I also need to make custom tooltips with the originial measures in thousands. Hope my question make sense and I would really appreciate if you could give me any guidance.

Great video. How can I format the number 1.00054E+16 to 1.00054. Thanks

Thank you.👍

@HowtoPowerBI

3 жыл бұрын

thx for watching Bharath! 🙂

@Bharath_PBI

3 жыл бұрын

@@HowtoPowerBI You are welcome. 1 quick question, if we have to set the format strings for a field in a large model, which is on Premium workspace, without making any changes to other properties of the table, how can we achieve that?

@HowtoPowerBI

3 жыл бұрын

@@Bharath_PBI maybe ALM toolkit is an option for you to make incremental changes alm-toolkit.com/

@Bharath_PBI

3 жыл бұрын

@@HowtoPowerBI Yes we use ALM toolkit, however, if I make some changes in model along with power query changes, and then want to deploy only model changes without PQ changes then ALM toolkit doesn't provide this..

How do you format text string, lets say you want to keep leading zero's, when you change to number it drops these values?

This is exactly what I was after!

@HowtoPowerBI

3 жыл бұрын

awesome, happy to hear it helped! 😀

I have switch statements in which I have format and colour the value If it >0 .. please help .. I cannot use conditional format for whole column or row . Just that value

Very informative tutorial. Also I cannot change comma to dot for thousands separator because I am from Europe. Anyone can help ?

Hello, Very useful informations. Quick question, could you tell me how to fix this problem : when I import data from PQ, the decimal numbers use a dot based on my local settings and this is what I want. But when I create mesures or use a column in a visual, the decimals show commas instead of dots. Just to be clear, it shows in my dataset for exemple 0.36 but in my graph 0,36. How can I fix this to only use dots. Thank you.

@HowtoPowerBI

3 жыл бұрын

Thank you Jerome! How the values get displayed in the visuals (dot vs comma) depends on the user's regional settings in windows.

Thanks a lot for your excellent explanation. I have a tricky question... how to format a value in thousand but in an upper comma. instead having 2.339, by using #,### , I want to have 2'339. The format #' ###, doesn't work :/

@HowtoPowerBI

2 ай бұрын

#'###'##0

Which beard Oil do you use? sorry if its out of syllabus question😄

How to use format strings with conditional formatting in DAX please

Can I custom foamatting at text box (1000 to 1K) ? I didnt find custom menu.

@HowtoPowerBI

2 жыл бұрын

when you adjust the formatting for the measure that you added to the textbox it will update in the text box (you can't use the custom formatting strings in the dialog box for inserting the value into a text box).

Great video tips but please could you maximize the display tab rather than your own picture and that way viewers have a better view on a maximize screen. Thanks

@HowtoPowerBI

2 жыл бұрын

thanks for watching and the feedback 🙂

Busy hunting down how to display a decimal as time in dynamic formatting, if i use "hh:nn:ss" or "#,hh:nn:ss" then on the matrix visual displays hh:nn:ss.

@MrSparkefrostie

8 ай бұрын

Now for what I am up to, I have a summarized table with a crossjoin, I have a date column and (both name and key) column for country and measure/metric. Some are Whole numbers, others are decimals and others are % and the one I cannot sort is time. VAR MeasureKey = SELECTEDVALUE(MeasureKey column) return SWITCH(TRUE(), MeasureKey = 1, "#,0", MeasureKey = 2, "#,0.0", MeasureKey = 3, #,0.00%, MeasureKey = "hh:nn:ss") using the matrix visual I have added the dates on Rows, measure name on the columns first, country second and then used the combined drill on the columns. I can now see the result of the measures grouped by countries and the dynamic formatting allows me to switch when I need whole numbers, decimals and percentage, it's just time/duration

@MrSparkefrostie

8 ай бұрын

And then I find it, replace "hh:nn:ss" with FORMAT(SELECTEDMEASURE(),"hh:nn:ss") next one will be getting more than 24 hours or more than 59 minutes or seconds without doing that text conversion, but may be needed

Hi, what about color? Positive value show in green and negative value in red. How can we do that?

@HowtoPowerBI

3 жыл бұрын

that you can't do with custom formatting (in contrast to excel). You have to use conditional formatting instead.

What if I don't want for my values less than 10,000 to have it written in K but rather as it is? How should we write the format? E.G. 1,000 as 1,000 and not 1K 15,000 as 15K

What is the difference for using this method instead of FORMAT ?

Aleluya, that's cool

@HowtoPowerBI

3 жыл бұрын

👊 awesome thx!

@How to Power BI Hello ! Great video !!! As usual ;-) Very clear. However, I can't get the format string to format my value in thousands in a PowerBI matrix. Just like in your example, I'm trying to format 1234567.12 to 1,235 K, but I'm not succeeding. Thousands separators get added but the original value doesn't get scaled. I've created this measure : TEST_FORMAT = 1234567.12 Basically, when I format 1234567.12 with this format string "#,##0, K" (obviously, I'm not using double quotes) , I'm supposed to get "1235 K" which is what I want. However when I drag the TEST_FORMAT measure in a card or in a matrix "1.234.567 K" is displayed. The very weird thing is that in PowerBI, below the format string input box a preview of the formatting should be displayed. When I use your format string "#,##0, K" I see this : "Example: 123 K" and in the report the visual displays "1.234.567 K" . I've tried using tabular editor too, in order to set the format string, but nothing changes, same result ! Thanks in advance for your help

@WalterFJR

Жыл бұрын

Try this #,##0,K,

@JennaJohn

Жыл бұрын

@@WalterFJR thanks this is what i needed

Waiting for the possibility to format weekly opening hours: 60:00 ... or monthly/yearly sold/purchased hrs etc ...it's just formatting

@HowtoPowerBI

3 жыл бұрын

you cant? 🙄

i have values in indian formats like this 2,23,92,800 but i want the values in foriegn format like this 22,392,800 how can i do this in by custom formatting? what commands in need to give for this ?

@HowtoPowerBI

2 ай бұрын

you tried to use the custom formatting string as in the video and it doesnt work ? ( for example #,##0.0 )

Thanks for Posting such informative video on formatting. Can u please confirm whether we can use [>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0 in Power BI which works in excel but not in power bi? Your response to above query would be highly appreciated.

@how to powerbi I am from India. I need to make the number to lakhs or crores. How to define the custom format string for that? Thank you in advance

Hi Bas, As per the requirement I need to implement as Thousands to M, Millions to MM and Billions to B. Please let me know how I can achieve this.

@HowtoPowerBI

3 жыл бұрын

here you go 🙂 #,##0,.0 M #,##0,,.0 MM #,##0,,,.0 B

@Hussain-kd7xh

3 жыл бұрын

@@HowtoPowerBI Thanks for your reply.

@Hussain-kd7xh

3 жыл бұрын

It is work for measure values also?

@HowtoPowerBI

3 жыл бұрын

@@Hussain-kd7xh yes, also works for measure values :)

@Hussain-kd7xh

3 жыл бұрын

Ok Thank you so much.

what if i have let's say 10 which is actually milions, how i will use strings to have it as milions in my report

I can't change negative value to red. Usual custom formatting #,###;[red](#,##0) will become #,###;\[ \e\d\](#,##0) in power bi

@HowtoPowerBI

2 жыл бұрын

in power bi you can't set the color with the formatting strings as you do in excel. For that you need to use conditional formatting (also mention it somewhere in the video).

Need separator like 1,22,33,44,55,666

I guess a number format for grams, kilograms and tons would follow same principle as thousands, millions, billions

nice video, but what about changing the decimal and thousand separators? (regardless of the language of the pbi desktop or tenant admin settings or user local configs. :) ) ex: i have "two hundred and sixty-seven thousand and four hundred and ninety-three" and 33 i'ts the decimal place 267,493.33 ... to (always) ... 267.493,33