Using Custom Format Strings in Power BI Desktop

Hi, Please help me out, I need comma after three digit, when I changed my setting as per your suggestion in data view value showing with comma after three digit but report view still comma showing after two digit, Kindly support..

super, would like to know how do make one row as number another row as %

Hi,
Great video!
Can you have custom format use a column/measure instead of a hardcoded currency sign? Say you have different currencies.

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 Patrick this formatting is working fine in table, card but not working fine when it comes to chart line line char, bar graph any suggestion or workaround.

i need to format this number. 09802.0228 ... how do i do that in power bi? it has 5 numbers plus a period (.) plus 4 numbers after the period. any help is appreciated

Hello, I have one doubt. I was working on waterfall chart in Power bi RS version. In that I have applied custom format (0;0;) to hide the negative sign in visual which works fine but I also need the values to be seperated by comma which I am not getting an option. Any Idea how can I bring a comma in those values.
PS- I have tried the things you told in this video but that also not working.

Is there a string that automatically formats numbers to show as K , M and B?

Is it possible to change text within a new measure example: Comment = "Thank you". Can I change the color of the words? Can I move 'You' to a second line? etc

Hi, I am trying to format large number as xxxM (million). For example, 10750459648 as 10,750M. Any clue? Thank you

This is such a helpful video. I have a data set with numbers where '15' means '15 Million' - can I use this technique to display the numbers correctly as $15,000,000

Nice content as always. How about if I'd like to show the values in K (thousand), M (million), B(billion), automatically? Best

Thank you for video. It is very useful but I didn't find that I am looking for. I would like to write 1000 as 1 K and 1.000.000 as 1 M in the table. Any idea? Thanks

I created a report on Desktop back in January 2021 using this Currency Formats . The March 2021 update corrupted the formats and I had to recreate them. Does anyone have any idea why that might have happened?

Hi, my decimal in my .csv file is 'dot' but, Powerbi treats those number as 'Text', any suggestion I can change the type to 'number'? thank you!

For negative numbers, you have explained how to put number between brackets - Would you please let us know how to get a negative number colored in red instead of brackets? - Many Thanks Patrick!

Thanks for this! This has helped me resolve my number formatting on a chart I have been struggling with!

hey buddy i am not getting formating tool bar on top ..should i have to download a different version??

This is very helpful, thanks. Just one question, when I export the data of the custom format decimal column (#.##) into excel i get an extra dot for whole numbers. eg. 7. / 10. How this can be rectified?

Thank you! But QQ, After formatting number using #.##, it is showing correct in PBI service but when exporting to excel, it adds DOT(.) to the values which have 00 as decimal values. Example, 5.00 is shown as 5. in excel. Any solution?

when field formatting to "Millions", how can I get it to show MM instead of M? in my company, M stands for thousand and MM million, so it's causing a lot of confusion. I'd rather not make a 20 new columns just to divide by a million and format these 20 new columns... (Ex: 1M -> 1MM)

Wow thats a life saver. Can we also do something like this for different financial ratios? For example, PE multiple has "x" in the end. Can I get that in PBI?

Is it possible to put data level with different format, like in bar chart i want to some bar value in thousand and some in million. Because if i set value format as million then some bar having less value and it's difficult to understand right value. That's why i want to show thousand and million as data level in same bar chart.

What about syntax for colors, for example #.##0.;[Red]-#.##0. ? It does not work for me in Power BI.

How to make affect the custom format to date filter..?
It seems to be always set itself to dd/mm/yyyy
I want to have ex:- 10-Apr-2020

Thank you so much. You got me to the area I needed to resolve my issue.

Helpful thank you man

We need milliseconds in Power BI for both display and DateDiff please! Also, you can't graph time formats on the y axis like you can in excel, much needed. For instance, showing the average start time graphed across other dimensions on the x axis. At least not without converting to / displaying in decimal format

What about currencies, in a multi currency environment? When the currency, like USD, EUR ..., is specified in a separate column. You would like to see 10 USD or 15 EUR, in a measure you cannot aggregate the different currencies together. Maybe you want to use currency signs, like $ or €, and for some currencies place the sign in front of the value and other behind.

Hi Patrick, great video again! One question. Is there a need to enable the option of custom formats? In my power bi this option is not shown unfortunately and I could not find out why so far... any idea? Depending on a version or something? Thanks a lot in advance. Best regards

Your introducing of confusing content is something interesting and funny😅

Thanks! Do you have any suggestion how I could implement a custom format depending on a parameter table? For example, I have a parameter "Scale" (kB, MB, GB, TB). If I chose MB, than the measure is calculated as "value/1024/1024". Is there any way to set the format on 0.00"MB" depending on this parameter?

OMG! So helpful--for Excel too!! I often have been asked what do all those symbols mean in the custom formatting in Excel, and now I know.

Hi Patrick,

I wanted to know if there is any option to sync slicer with Bookmarks ? Dynamic Bookmarks ?

Please make a video on the same if it is possible.

You are great in what you do !!!

Thanks,
Deepanshu

Interesting vid as always! Could you use this technique to format a column with both thousands and millions values, with thousands shortened with an appended "K" and millions with an appended "M"? e.g 20,500 becomes 20.5K and 3,500,000 becomes 3.5M. From what I understand you can't mix formats in cards, etc.

wow great Pats

Hi, how do you zoom in a section of the screen ?

Customer Formatting is great ... But you need to be able to set it differently on various visuals.

Wow! I had no idea that you could manually customize the date format. I just came across an instance of where I wanted the date in the MMM yyyy format, but was surprised there was no "custom" format option under the modeling tab in the ribbon. There are quite a few other options, but not the one I needed. I had to settle for the full month name & year (MMMM yyyy) I would have never found the custom option under the Model view if it wasn't for your video. 🤦‍♂️ Thanks so much!!

Any way to truncate thousands or millions to $1K or $1M?

Hello All-Knowing Power BI Masters! I am not sure if you read the comments, but I could use some advice on a particular use case and how Power BI might help. I am looking for a visual to best display listing of events compared year-to-year that would allow my organization to get insight into them.

More info: I work at a K-12 school and our “years” are defined as July 1 – June 30 (this year being July 1, 2019 – June 30, 2020). Each spring and summer we have a team of people try to draft the next years calendar of activities (Key Dates). This would be everything from new employee orientation, first day of school, experiential ed, winter break, holiday, religious observances, spring break, finals, etc. Basically, every important event for the entire year, which is a LOT of data. I have been asked to help the school better streamline this process using technology.

The team would like to review previous year’s key dates side-by-side with current drafted dates and even potential future school years. Outlook group calendars tend to get oversaturated and not as easy to compare years. Being a fan of Power BI, I was thinking if there was a way to pull this into a report/dashboard for comparison. We could enter the prior dates/events into an Excel workbook and pull it into Power BI. Here is where I could use help. What would be the best way to visualize this data? I would like to find a way to show side-by-side lists of dates and events (as lists?). Also, data like counts of how many total school days, holidays, etc. I think the filters could help digging into specific kinds of key dates. I could see us growing this into a critical dashboard reviewing what is going on in the school year.

Any suggestions on how you would display this? Thank you!

It really helps, thanks a lot!

Advanced Excel users will shine in using this functionality as it uses the same custim number formatting structure in Excel..love it

thank you for the videos. I am watching the series every day. Please explain the RLS for multiple rows. your help is always appreciated.

Can you send your t-shirt to my address , its very inspiring

Great as usual Patrick - would be even better if the custom string itself could be a measure. I'm thinking of the scenario where you have a measure which shows different things based on a selection (e.g. Order Value, Average Basket Value, Product Quantity or Conversion Rate). You ideally want that measure to be formatting variously as $#,##0, $#,##0.00, #,##0 or 0.0% depending on what's it's showing at the time. I know FORMAT can do this in say a Card, but if using the variable measure in other vizs we need it to be treated as numeric and FORMAT makes it a string. Maybe you can talk to your colleagues about this improvement?

Is there any benefit over doing this compared to using FORMAT ie FORMAT(DateTable[Date], "MMMM")? Is it efficient or lazy :)

I really hope that you give us more videos about Power BI Desktop 🤞🤞 And your T-Shirt is awesome 😍😍

I am looking for a job to work on power bi, sir.