CODEWITHSUNDEEP
excelpivot-table
George
George

Reputation: 347

% of Row Total in pivot table

I'm trying to format my pivot table values as percentages of their row totals (in my data, each quarter). However, when I select Show Values as % of Row Total, it gives each value 100%.

When I try Show Values as % of Parent Row Total, it uses the column as the total and uses the first 4 quarters of 2017 as % total of 2018, not at all what I want to do.

Here is my current pivot table enter image description here

Here is how I'm trying to format the same pivot table enter image description here

Upvotes: 0

Views: 4248

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

I didn't type all your "types" (or whatever is in the columns), but when I summarize by % of Row Total, it works as expected.

pivot table with % by row

Is yours set up differently than what I show?

Update

If your data points are all separate columns as @jeffreyweir suggests, then you can't summarize them how you want using pivot table features. You'll have to add some columns to your data

table with percent calcs

Each Pct column is a formula:

=[@Replacement]/SUM(tbldata[@[Replacement]:[DTK]])
=[@Refund]/SUM(tbldata[@[Replacement]:[DTK]])
=[@Parts]/SUM(tbldata[@[Replacement]:[DTK]])
=[@DTK]/SUM(tbldata[@[Replacement]:[DTK]])

Then you can sum those in a pivot table.

pivot table summing calculated fields from a table

Upvotes: 1

Related Questions