Welcome to the DAX:FORMAT post, where we’re going to learn the basics of the FORMAT function to format numbers for different circumstances.

We’re going to use a simple Power BI file to get to know how to do this. If you’d like to use the same file, you can download it here.

Video Post:

Written Post:

To learn the basics of the FORMAT function, we’re going to use a data column that has percentage data in it. Our Values column has percentage values in it, written in decimals.

You can see that I already have a Table visual as well as a Card, both using our Value column.

You may want to format the Value column in these visuals to make it look like a percentage, and there’s a button to do this. If we select out Value column in our Data Pane, we get a Column tools ribbon along the top of Power BI, along with ways to format this column.

When we click that % button, our Value column will now show up in visuals as a %. You can also set the number of decimal places to show.

This is all fine and good if you want your value to show up the same way everywhere, but often times you don’t want it to be the same everywhere, as it may be for different purposes.

Perhaps you want to keep those 2 decimal places in the Value column for a visual like the Table, but for the card (or maybe a label on a bar chart), you may NOT want any decimal places, as your viewers probably don’t need that level of detail on those visuals.

We’ll use the FORMAT function to create a measure with a variation of our Value column. Try this code:

Value No Decimals = 
FORMAT(
    SUM('Table'[Value]),
    "0%"
)

The FORMAT function has two parts.

The first part is WHAT we’re formatting. In this case it’s the SUM of our Table value.

The 2nd part is HOW we’re formatting it. In this case we just want it to be a percent with no decimals, so we put “0%”. If we wanted, say, 3 decimals, we’d put “0.000%”

Now we can add this new measure (instead of our Value column) to our data card, and it is formatted to have NO zeroes.

Pretty cool, right?

Microsoft has a page FULL of different ways to format different types of data, and we’ll do more posts about formatting using the FORMAT function for sure.

Take care everyone,

Joe.

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.