Have you ever wondered why formatting numbers is so important in Power BI? Are you struggling to figure out ways to format numbers in Power BI?

Then this article is for you.

Making numbers easy to read is crucial for people working with data because it allows you to make people understand the information quickly and precisely.

This article will focus on converting the numbers into comma-separated two-decimal numbers. The specific format for it is "#,0.00". I will walk you through various ways to apply this custom format in Power BI.

You can apply it through the Power Query Editor, Model view, Report, or Data view, using a DAX formula, as well as through the Display Units in Values option of the format of your visual section.

You can use these ways to apply different custom formatting to your columns containing numeric values.

Let’s fix the appearance of your numbers! 😁

The Dataset

For this demo, I am using the above fictional sales order data in an Excel file with around 13 columns and 7992 rows, which looks like the one shown below.

Power Query Editor Way

As said earlier, the well formatted numeric values are easy to understand for users. The Power Query Editor allows you to format the numeric column as a comma separated two decimal numbers column. During the process, it provides you with a seamless way to integrate this format into the database and simultaneously allows you to customize the format based on the M-Code.

Through this section, I will walk you through how to get this done through the Power Query Editor.

1. Open the Power BI Desktop app and click on the Get Data dropdown through the Home tab. You will see the Common data sources as a list. Select the Excel workbook option to add data from the Excel workbook.

2. It will open the the File Explorer. Navigate to the folder where your data is stored. Select the file Sales Analysis Report. Click on the Open button.

3. Click on the Sales_Data sheet through the Navigation window that opens up. It will show you a preview of the data on the right-hand side. Since you want to change the format of the numeric columns to comma-separated two-decimal values, click on the Transform Data button below.

Inside the Power Query Editor, you will notice that the columns Unit Price, Total Unit Cost, and Total Revenue appear as whole numbers. You want to change their formatting.

4. Select three of the columns together > Click on the Data Type dropdown inside the Transform tab in Power Query Editor > Select the Fixed decimal number format.

5. On the Change Column Type window, click on the Replace current button. It will change the formatting to the current step. If you want to keep the original formatting as it is, click on the Add new step, which allows you to add another step for this format-changing activity.

Now look at the columns appearing as comma-separated two decimal values.

Using Power Query Formula

In this section, you will learn how to format the numbers as comma separated two decimal numbers using built-in functions in Power Query Editor.

You can create a new column in Power Query Editor and apply the format #,0.## using a custom function Number.ToText. Let’s explore how.

1. With data loaded into Power BI, click the Transform option from the Home menu to launch the Power Query Editor.

2. Go to the Add Column menu and select the Custom Column option to add a formulated column to the data.

3. Inside the Custom Column window that pops up, rename the column as Total Unit Cost Formatted through the New column name textbox and add the following formula to apply the formatting “#,0.##.” Click on the OK button.

= Number.ToText([Total Unit Cost], "#,0.##")

The newly formatted column looks like the one above. You can use it in any visual on the Report view.

Note: The only problem with this approach is that it converts the numeric values to strings while applying the formatting. Therefore, if you try using it on Report view, it will not show you the values summarized.

Through the Model View

The Model view in Power BI is the backstage where you shape and restructure your data. You can not only visually design and modify the data model and manage tables and relationships between all the tables available, but you can also format the columns the way you want by altering their formats.

For this section, you will be utilizing the same powers of the Model view to format the numbers into comma-separated two-decimal numbers.

1. Accessing the Model view is quite simple. You will see on the left side a vertical navigation pane with three options in it. Click on the last one that represents the Model view.

Note: This vertical pane will be accessible all the time when you are in the Power BI Desktop app. Exception for when you are in Power Query Editor. At that time, you will not see it.

2. Through the table structure, click on the column for which you want to change the number formatting. Since you already changed the formatting for the Total Unit Cost in the last section, go with Total Revenue this time.

3. On the right-hand side, you will see the Properties pane collapsed. Click on the arrow next to the Properties pane to expand it.

4. Expand the Formatting dropdown inside the Properties pane > Select Data type as Decimal Number from the dropdown > Choose Decimal number in the Format dropdown > Click on the Thousand separator toggle button to add the comma to your numbers > Decimal places can be set as 2 to show the values in two decimals.

This approach should allow you to see the numbers as comma-separated values with two decimals. You can see it on the Report view by adding a Table or Matrix visual, as shown in the screenshot above.

However, the format you are looking for most of the time might not be available through the selected dropdowns such as General. For such cases, you can choose to apply a custom format of your own in the Model view.

5. Inside the Formatting pane under Properties > Make sure you have selected the Data type as Decimal number > Choose Custom from the Format dropdown > Below that, a new text box appears named Custom format. Type in #,0.## there to apply this formula to the Total Revenue column. It also shows how the values will appear through the “Example:” section.

This approach is more convenient when you want to apply a specific format of your own to a specific column.

These were the two ways to format numbers through the Model view in Power BI. If you were thinking it is just used to view and modify the data tables through it, I am sure this demo must have forced you to think beyond that.

Through the Table and Report View

Similar to the Model view, you can also change the number formatting through the Table and Report view. The approach is the same, just the way of getting it done is different.

Follow the steps below to change the number format through Table view.

1. Go to the Table view and select the column named Unit Cost. This is the column you wish to change the number formatting for.

2. Now, as soon as you select the column, you will see the Column tools appearing on the menu bar. Click on it, and You can see that the system picks the format of the column as a Decimal number with zero decimals.

3. Under the Column tools, click on the Comma under the Formatting group to add the thousand separator. After that, change the number of decimals to 2 places.

Check the column now, and it appears as comma-separated two-decimal numeric values.

4. However, most of the time, you will be interested in applying a custom format to the numeric column. To achieve this, type in #,0.## under the Format section inside Column tools.

This will return the same formatting to the Unit Price column.

Using the DAX Formula

In Power BI, there is always a way to achieve anything using the FORMAT function.

The function is used to format the values of one type to another type. This function can be used to format the numeric columns to the values with a specific format. In this case, the format would be #,0.##.

The syntax of the FORMAT function is as shown below:

=FORMAT(<value>, <format_string>[, <locale_name>])

Where,

value – represents the value or an expression returning a value to change the format.

format_string – is a string that represents the format you want to apply.

locale_name – an optional argument specifying the locale to be used by this function while applying the format.

Let’s use this function to apply a specific formatting to a numeric column from the table.

1. With the data loaded in Power BI Desktop, click on the table. You will see the Table tools appearing on the menu tab. Click on it to see all the options. Select the New column option from the Calculations group. This allows you to add a new calculated column to the table.

2. In the formula bar, type in the following DAX code to add the custom formatting to the column Total Unit Cost.

Total Unit Cost Formatted = FORMAT('5 - Using the DAX Function'[Total Unit Cost], "#,0.##")

3. Now, add this column to the Table or Matrix visual and see the number as comma-separated two-decimal values.

Note: The FORMAT function alters the appearance of the numeric column. It now appears as the text valued column. Because of this, you cannot summarize the values from this column.

By Setting the Display Unit through Format Visual Option

The final method is a simple alteration where you change the Display Unit and set it to None to make the number appear as a comma-separated two-decimal value.

The method is crisp and straightforward to use and understand, which makes it a go-to method for most of the users. Let’s see how to do it!

1. In Power BI, go to the Visualizations pane and click on the Card visual to add one to your report. Select the Total Revenue column from the table inside the Data pane to display the sum of total revenue on the card.

Note: You can also drag the column under the Field section, provided the visual is already selected.

2. With the Card visual selected on the Report view, click on the Format visual option inside the Visualizations pane.

3. Expand the Callout value dropdown from the Visual tab, which is selected by default under the Format visual option. You will see that the Display unit is set to Auto by default, and Value decimal places are also set to Auto by default.

4. Change the Display unit to None from the dropdown and type in 2 under the Value decimal places section. Now, if you look at the visual, it shows the values in comma-separated two-decimal values as expected.

Pro Tip: You can also set the Value decimal places by using the up and down arrow in front of the textbox. They increase and decrease the decimal places by 1 unit, respectively.

Pro Tip 2: Even if you keep the Value decimal places as Auto, it will still do the job for you as it shows two decimals by default.

Setting Display Units in Matrix or Table Visual

This method also works with the Matrix or Table visual to show the values in comma-separated two-decimal values.

You just need to go to the Specific column section > Select the specific column from the dropdown for which you want to set the Display unit to None and Value decimal places to 2 decimals.

Expand the Values section > Set the Display unit to None and Value decimal places to 2 decimals.

Setting Display Unit in Any other Visual

Moreover, you can set the Display units through the Data labels section if you are working with any other pictorial visual, such as a Bar/Column/Pie/Scatter chart.

1. Make sure the visual is selected in Report view > Go to the Format visual option and select the Data labels dropdown

2. Expand the Values pane, and you will see the Display Units dropdown. Select Custom from the dropdown to apply a custom number formatting of your own to the data. In the Format code textbox, type in #,0.## as a formatting code and see the chart now. It shows the data labels as comma-separated two-decimal values.

Pro Tip: Similarly, you can change the formatting for the values appearing in millions on the X-axis. There is a specific dropdown for X-Axis and Y-Axis inside the Format visual option.

Note: The Data labels and Values option are not active by default. Click on the radio button next to the option to activate it. If On, it means active. If Off, it means inactive.

This method, by far, is the easiest one that doesn’t involve any additional column to be created. It also marks the end of this article here! What have we learned?

Conclusion

You can change the number formatting to comma-separated two-decimal numbers in six ways.

The first method is altering the format as Fixed Decimal Numbers inside the Power Query Editor. Provides a hardcoded solution where the formatting is hardcoded and loaded into the model the same way.

The second method involves creating a Custom Column inside the Power Query Editor to apply the formatting. This method, however, changes the values to text, and you can’t summarize the values inside any visual.

The third and fourth ways are pretty easy to follow, where you change the custom formatting to #,0.## through either the Model, Table, or Report view—easy to follow and moderate efforts.

The fifth method involves creating a Custom column again but in the Table view on the front rather than creating it at the time data loads. This method adds a custom format by changing the value types to text, which doesn’t help at the time of the summarization of the visual.

Finally, the Display units way allows you a comprehensive yet flexible way to apply the custom number formatting for all visuals except the Card, Matrix, and Table visuals, where the values are shown in comma-separated two decimal values.

Do you know any other methods to format the numbers? Let me know through the comment section below.