Do More with Pivot Tables - Value Field Settings




Pivot tables are effortless. It is not only capable of doing heavy data lifting and quickly summarize and analyze the data but can also perform some additional calculations.


Value Field Settings provides a plethora of options as quick calculations. This article covers my top 5 such features.

How to get Value Field Settings?

Value field settings offer plenty of flexibility for calculations. There are two ways we can access Value Field Settings:


Right-Click on any cells containing the value which we want to transform:


Under Values, click on the drop-down option arrow:











Percentage of Column Total or Row Total

The percentage is a crucial aspect of any analysis as it provides a baseline. Using a percentage, we can compare the performance of different periods or different KPIs.


The most common calculation is calculating the percentage of column total or row total. Value Filed settings come with extensive calculation options.

% of Column Total

Value Field Settings > Show Value As > % of Column Total


Output:









Analytical conclusion: Steve has contributed 23.95% to the total sales whereas Bob's contribution is only 0.91%.


% of Row Total


Value Field Settings > Show Value As > % of Row Total

Analytical conclusion: A majority of Bob's sale (approx. 70%) is from Corporate and Home Office segments. Overall, Small Business has the lowest contribution to sales(17.98%).

Percentage of Parent Total

When we have multi-level report structure, Pivot table provides the flexibility of choosing the denominator:


Analytical conclusion: Tables contributes towards 38% of sales in the Furniture segment and 13% on the overall product category. Technology has the highest contribution to sales(approx. 42%).

Value Field Settings > Show Value As > % of Parent Total > Select the Parent Category


Percentage Running Total

This one helps in prioritizing the top and bottom contributors. We also know this as the Pareto Rule or 80/20 rule.


Output:

Analytical conclusion: The highlighted segments contribute towards approx. 80% of the total sales volume.


We can also get the absolute number (Select Running Total In)

Percentage Difference from Previous Row or Column

Another related useful feature is that we can calculate the percentage difference from the previous row or column. This type of calculation is handy when we compare from one period to another.

% Change from the previous row

Value Field Settings > Show Value As > % Difference From

Output

Analytical conclusion: There is a 5.23% reduction in the total orders from the year 2010 to 2011.


% Change from the previous column


Summary data:

% change from the previous column:


Analytical conclusion: Sales for the Atlantic region for the year 2011 has reduced by 20.32%.


Add Ranking

The ranking is handy during analysis. In Excel, we can use functions like RANK.AVG or RANK.EQ. Pivot provides a drag & drop option to calculate a ranking.



Analytical conclusion: Our top 3 ranked products are Office Machines, Telephones and Communication, and Tables.

And the best part about the pivot is the dynamism it provides.

You may follow this article for more on Ranking: Use Ranking for comparison



Less effort, more output!


@imVivRan

Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING