BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Learn how to create, format and explore pivot tables within Excel based upon SSAS tabular models. This tutorial also shows how to create slicers, and combine them to create dashboards.
- Excel pivot tables in Analysis Services (Tabular Model)
- Setting up pivot tables in Excel
- Refreshing a pivot table
- Formatting numbers in a pivot table (this blog)
- Formatting a pivot table and choosing display options
- Drill-down and Quick Explore
- Slicers
- Things you can't do in tabular model pivot tables
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 24 November 2015
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Formatting numbers in a pivot table
There are two ways to control how a measure's numbers appear in a pivot table: by changing the value field settings for the measure, or by changing its underlying model's Format property.

All of the examples below show how to change the format of the average price measure below to show just two decimal places.
Changing a measure's value field settings
You can change the format of a measure in a pivot table by going to its value field settings. Either like this:

You can click on the arrow next to a measure in the pivot table field list and choose this option.
Or like this:

You can alternatively right-click on any single cell in a pivot table and choose the option shown.
Note that although you only have a single cell selected for the second method above, it will still change the format of every statistic displayed for this measure.
You can then choose to change the format of the numbers calculated for this measure:

Click on the Number Format button at the bottom left of the dialog box which appears, as shown above.
You can now change the number format in the standard Excel way:

Here, for example, we're setting the numbers to display with two decimal places only.
Better!

The average prices are now easier to read.
Alternative method: changing the model's default format for a measure
Perhaps a better way to change the format of a statistic is in your model - then all client tools such as Excel pivot tables will use this format by default to display it.
To change the default format for a measure like this, first select it:

Click on the measure whose format you want to change (you'll find your measure in the grid at the bottom of the table to which it belongs).
Display the properties window for the measure (the easiest way to do this is probably to press F4), and change the default format for the measure:

Here we're setting the measure to display with two decimal places. You can use the Show Thousand Separator property to divide thousands, millions, billions, etc.
When you save your model and refresh the pivot table, the number formatting will update to the setting you've chosen.
Note that if you then change the value field settings for the measure in a pivot table, this will override the measure's default format property (as you would expect).
Having looked at how you can change how numbers appear, let's now widen the net and look at how you can make more general changes to the formatting of a pivot table.
- Excel pivot tables in Analysis Services (Tabular Model)
- Setting up pivot tables in Excel
- Refreshing a pivot table
- Formatting numbers in a pivot table (this blog)
- Formatting a pivot table and choosing display options
- Drill-down and Quick Explore
- Slicers
- Things you can't do in tabular model pivot tables