Better Calculations with Show Values As
January 23, 2023 - by Bill Jelen
Problem: Excel offers eleven functions on the Summarize Value By tab of the Value Field Settings dialog. Those are not the good ones. The good ones are on the Show Values As tab. Ninety percent of the time that I change the calculation, I am using one of the relatively hidden Show Values As calculations.
Strategy: Drag the Revenue field multiple times to the pivot table Values area. Select a cell in one column, use Field Settings and change the Show Values As.
Gotcha: The calculations require one, two, or zero arguments. An example of each follows.
The numbers in C4:C10 use the % of Column Total setting. You simply choose this setting. You don’t have to specify any additional information.
Rank and Running Total are examples where Excel will ask you to identify the base field. Most often, this will be the row field.
% Difference From is a calculation that requires a Base Field and a Base Item. The calculation in F4:F9 expresses revenue as a percentage of Manufacturing revenue.
The (previous) entry in the figure above is great for reports with dates. This report shows the sales as a percentage change from the previous day.
Additional Details: You can combine the 11 functions on Summarize Values By and the 15 settings under Show Values As. The figure below is showing the average sale for each day and then the % change from the previous day of the average sale.
In this figure, the percentages in C16:C18 express the revenue as a percentage of the total sector revenue in B15. The confusing part is that the 14.35% in C15 shows how the Communications sector total of $962K compares to the grand total of $6.7 million. All of the percentages are correct, it is just strange to see a smaller number on the total line than on the detail lines.
This article is an excerpt from Power Excel With MrExcel