Database Reference
In-Depth Information
Formatting your calculated columns
You often need to change the formatting of your Power Pivot columns to appropriately match the
data within them; for example, to show numbers as currency, remove decimal places, or display dates
in a certain way.
You are by no means limited to formatting just calculated columns. You can format any column; just
click in the column you want to format and use the tools in the Formatting group on the Home tab of
the Ribbon.
Veterans of Excel PivotTables know that changing PivotTable number formats one data
field at a time is a pain. One fantastic feature of Power Pivot formatting is that any
format you apply to your columns in the Power Pivot window is automatically applied to
all PivotTables connected to the Data Model.
Tip
Referencing calculated columns in other calculations
Like all calculations in Excel, Power Pivot allows you to reference a calculated column as a variable in
another calculated column. Figure 3-13 shows a calculated column called Gross Margin. The formula
bar shows the calculation is using the previously created [Total Revenue] and [Total Cost]
calculated columns.
Figure 3-13: The Gross Margin calculation is using the previously created [Total Revenue] and [Total Cost]
calculated columns.
Hiding calculated columns from end users
Because calculated columns can reference each other, you can imagine creating columns simply as helper
columns for other calculations. You may not want your end users to see these columns in your client tools.
In this context, the term “client tools” refers to PivotTables, Power View dashboards, and Power Map.
Similar to hiding columns on an Excel worksheet, Power Pivot allows you to hide any column (it
doesn't have to be a calculated column). To hide columns, select the columns you want hidden, right-
click the selection, and then select Hide from Client Tools.
 
Search WWH ::




Custom Search