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.
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.
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
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.
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
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 :/
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 ?
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?
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
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 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 UA-cam. 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!
@@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
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.
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
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
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
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 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.
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!
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
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.
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).
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
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.
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!
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).
@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
@@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 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..
@@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
Nicely explained.Tricky ...
Soon for 200 K (#,###, K) subscribers.Good Luck....
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.
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.
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
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.
Thanks for delivering this content. I really think you are the best BI educator out there
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
How to use format strings with conditional formatting in DAX please
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 :/
#'###'##0
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 ?
you tried to use the custom formatting string as in the video and it doesnt work ? ( for example #,##0.0 )
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?
h:nn:ss
Which beard Oil do you use? sorry if its out of syllabus question😄
The best video I have seen explaining the types of format. Brilliant!
Wow, thanks Isidre! Very happy you liked it!
This doesn't work for me :(
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
thanks for watching and the feedback 🙂
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?
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)
@@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 UA-cam. 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!
@@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
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.
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
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
Thank you very much
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
So good, thanks !!
thank you for a good video with very useful practical information!
Glad it was helpful! 😃
Fantastic! I've been waiting for this for quite a while! Thanks a lot Bas
Glad you like it! Thanks for watching Pedro 😀
Great video.
How can I format the number 1.00054E+16 to 1.00054.
Thanks
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
@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, 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.
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!
awesome, thx for your sub Peter! 👊😀
How do you format text string, lets say you want to keep leading zero's, when you change to number it drops these values?
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
Very informative tutorial. Also I cannot change comma to dot for thousands separator because I am from Europe. Anyone can help ?
Great content! Helped me solve the issue. Thank you for this informative video !!!
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.
I guess a number format for grams, kilograms and tons would follow same principle as thousands, millions, billions
useful info on custom formatting in power bi
Glad to hear that, thanks! 🙂
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
Is there an option to add like a button with which you can simply switch between two or more formats?
Need separator like 1,22,33,44,55,666
I can't change negative value to red. Usual custom formatting #,###;[red](#,##0) will become #,###;\[
\e\d\](#,##0) in power bi
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).
This is exactly what I was after!
awesome, happy to hear it helped! 😀
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
Hi Naga! Thanks a lot for your message and supporting the channel, much appreciated:)
Waiting for the possibility to format weekly opening hours: 60:00 ... or monthly/yearly sold/purchased hrs etc ...it's just formatting
you cant? 🙄
A clear and comprehensive explanation. Thank you!
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.
Thank you Jerome! How the values get displayed in the visuals (dot vs comma) depends on the user's regional settings in windows.
Lifesaver! Thank you!
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!
awesome, I'm very happy to hear it helped a bit! 😎👊
Can I custom foamatting at text box (1000 to 1K) ?
I didnt find custom menu.
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).
Super clear and well paced Video 👍
King of Power BI Bas!
Dude I love your Videos. They make Power BI Look so easy.
thank you so much, trying to keep it simple 😀
Hi, what about color? Positive value show in green and negative value in red. How can we do that?
that you can't do with custom formatting (in contrast to excel). You have to use conditional formatting instead.
Very helpful explanation how to use custom formatting
Glad you think so! thx for watching Emmaus! 😀
Awesome video, just what i was looking for, ty very much!
thanks for watching Juan, happy to hear it helped!🙂
@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
Try this #,##0,K,
@@WalterFJR thanks this is what i needed
Very useful video
Thanks a lot, great to hear! 😀
you are a star mate!! please keep your amazing videos coming :)
Will do Varun, thx!
Great and easy explanation! Thank you.
😊 🙏
Awesome...so helpful!
you aare the best man !
Hi thank you for the content. this content help me a lot.
that is great to hear! thanks Daniel! 😀
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.
here you go 🙂
#,##0,.0 M
#,##0,,.0 MM
#,##0,,,.0 B
@@HowtoPowerBI
Thanks for your reply.
It is work for measure values also?
@@Hussain-kd7xh yes, also works for measure values :)
Ok Thank you so much.
Thank you.👍
thx for watching Bharath! 🙂
@@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?
@@Bharath_PBI maybe ALM toolkit is an option for you to make incremental changes alm-toolkit.com/
@@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..
Thanks you are a genius. love your videos
So nice to hear, thx Kabir!!! 😊
@@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
Aleluya, that's cool
👊 awesome thx!
What is the difference for using this method instead of FORMAT ?