• Home
  • Formatting in Power BI: Part 1

Formatting in Power BI: Part 1

  • Rafat
  • May 21, 2022

In this article we will go over and try to understand formatting in power bi.
Firstly, we will go over formatting numbers in power bi while experimenting certain formulas on decimal numbers.

Formatting Numbers

Microsoft offers two types of formatting in general. Predefined formatting and custom formatting are the two forms of formatting. Predefined formatting, also known as conditional formatting, is a method of presenting data that applies unique formatting rules to the values of a specified metric based on some pre-defined parameters. It is most typically used to display data in a tabular format, but it can also be used in other types of charts such as bar charts and line charts. Custom formatting is basically telling Power BI how you want to present a number. The core data remains same; just how it appears on the screen changes.

Predefined Formatting

To format numbers in Power BI, first create a new column. Let’s call it “Predefined formatting” because that’s what we’re going to do first.
Since we need to pick a column containing values, we’ll open a sheet with numbers here.

predefined formatting power bi

The following will be the formula which we will enter:
Predefined formatting= FORMAT(‘Sheet1′[Numbers], “General Number”)
I imported the Numbers table from Excel, where “Sheet1” is the sheet which consisted of the numbers column and “Numbers” is the column itself.
Since the format is already established in general number, the format “general number” will display the same values.

There is just one little alteration that has to be made in the same formula to get different outcomes. That simple adjustment basically replaces “General Number” with any other format. Let us better understand this by actually replacing it with different types of formats.

The following are some of the different types of formats you can enter into the above formula:

Currency

If you replace “General Number” with “Currency,” a dollar sign will display before each value that represents the currency.

predefined formatting currency

True or False

Now, if you replace “currency” with “True/False,” It will show “True” or “False” for all the values that are in the column.

predefined formatting true or false power bi

Any integer greater than 0 results in a “True,” whereas any number less than 0 results in a “False.” If your data has all values greater than zero, the complete data set will result in “True.” Likewise, if your data has all values lesser than zero, the complete data set will result in “False.” If you get some “True” values and some “False” values, it signifies your data collection has both types of values, one greater than zero and one less than zero.

In the dataset which I used, we can see how every number value has formatted to “True” since they are all greater than zero.

Percentage

Now if you replace “True/False” with “Percent,” the predefined formatting will convert all the values into percentage. For example, if your value was 2.345, the predefined formatting would convert that value to 234.50% in the predefined formatting column. Let us understand this with my dataset.

predefined formatting percent power bi

Here you can see how 17.03 was formatted to 1703.00% and 2.36 to 236.00% and so on.

Scientific

If you replace “Percent” with “Scientific,” any scientific value that exists will be added in the column.

predefined formatting scientific power bi

You can easily obtain them and apply them as necessary.

This was all about predefined formatting. I hope you were able to follow me. Let us now head onto custom formatting!

Custom Formatting

One thing that you should know is that, the formula for custom formatting is EXACTLY the same as predefined formatting formula.


The following is the formula we used for Predefined formatting:
Predefined Formatting = FORMAT(‘Sheet1′[Numbers], “General Number”)
Where you can replace “General Number” with any other format.


The following is the formula we will use for Custom formatting:
Custom formatting= FORMAT(‘Sheet1′[Numbers], “General Number”)
Where you can replace “General Number” with any other format.

So let us first create a new column (right next to “Predefined formatting”) and enter the formula for custom formatting.

custom formatting power bi

The following are the formulas that you can enter for custom formatting:

#

Let us replace “General Number” with “#.” What “#” does is it removes the decimal values from the numbers and provides the exact value. For example, if your number column contains “6.3” the custom formatting will round it off to “6” with the use of “#.” Basically, this formula rounds off all the decimal numbers.

Let us understand this from my dataset. We can see how 17.03 was rounded off to 17 and how 2.36 to just 2.

#.#

If you replace “#” with “#.#,” it will simply show the decimal number after the number.

Now it won’t directly show “2.36” but instead will show “2.4” (It basically rounds off the decimal number on its own for you.) However, if you notice how 17.03 got formatted to “17.” and how there is nothing after the dot. There is a way to fix this too!

#.0#

If you replace “#.#” with “#.0#,” it will add a zero after the decimal point wherever needed. There may be cases where not all numbers are decimal numbers, that’s where this formula plays its role.

Now if you notice, 17.03 is rounded off to “17.03” itself unlike the last time where it was just “17.” Similarly, if there was a natural number like “6,” it would get rounded off to “6.0”. The reason behind that is that there is nothing after 6. since it’s not a decimal number. One more thing in this formula is that the other decimal numbers like 2.36 are shown as 2.36 itself , 60.03 as 60.03 itself. Basically in this formula, if a number has only 2 digits after the decimal point, then both the digits are appearing in the custom formatting. We can change this too!

#.0

If you replace “#.0#” with “#.0,” it will show only 1 digit after the decimal point in custom formatting column.

Now if you observe closely, you will see how 2.36 is shown as 2.4. It got rounded off to 2.4 and isn’t 2.36 anymore. The same has been applied for the other numbers.

I hope this article was helpful. We will go over other topics under formatting in the next article.

If you’re new to my articles, I have written many other articles focusing on Power BI which will provide you with basic knowledge required.