Pivot Table %

[ryandonovan22]

Hello.

I need some help with displaying my pivot table values as a % of the row total but nothing is working.

I have a pivot that looks like this[TABLE="width: 500"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]On Time Delivery[/TD]
[TD]Late Delivery[/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]142[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]216[/TD]
[TD]196[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]439[/TD]
[TD]92[/TD]
[/TR]
</tbody>[/TABLE]

I cannot workout how to change the values into a %. So for Jan, On Time Delivery should read as 75%.

Thanks in advance.

 

[bobsan42]

Re: Pivot Table % Help

right click on the pivot table field, select Value Field settings.
Select the tab Show Value as
In the dropdown box select % of row total.
But first Enable GrandTotals for Rows - if you don't get that displayed correctly (or at all) you will not get adequate %.
For Row total to work you must have a group field (e.g. Delivery) added to the columns section of the pivot table.

 

[ryandonovan22]

Re: Pivot Table % Help

right click on the pivot table field, select Value Field settings.
Select the tab Show Value as
In the dropdown box select % of row total.
But first Enable GrandTotals for Rows - if you don't get that displayed correctly (or at all) you will not get adequate %.
For Row total to work you must have a group field (e.g. Delivery) added to the columns section of the pivot table.

What is a group field and how do I add one?

 

[bobsan42]

Re: Pivot Table % Help

For example if On time Delivery and Late Delivery are coming from two separate columns in you data table then you cannot get a Row total - you can only get Row Total for similar data - data coming from the same column.
To achieve this you must have a column in your data - let's call it Delivery - The cells in this column should have values On Time or Late.
Then in the pivot table the Date/Time column goes in the row section, the Delivery column goes in the columns section, and it also goes as Count in the Values section.

Again, Only data coming from the same column can produce a Row Total.

The other way to do it is to have a column which holds the Total (lets call it Tot2) and then instead of % of Row total you can select % Of and then select Tot2.

 

[ryandonovan22]

Re: Pivot Table % Help

For example if On time Delivery and Late Delivery are coming from two separate columns in you data table then you cannot get a Row total - you can only get Row Total for similar data - data coming from the same column.
To achieve this you must have a column in your data - let's call it Delivery - The cells in this column should have values On Time or Late.
Then in the pivot table the Date/Time column goes in the row section, the Delivery column goes in the columns section, and it also goes as Count in the Values section.

Again, Only data coming from the same column can produce a Row Total.

The other way to do it is to have a column which holds the Total (lets call it Tot2) and then instead of % of Row total you can select % Of and then select Tot2.

Thanks for your response.

I have a field called Total Deliveries, which record the total number of deliveries regardless as whether they are late or on time. When I put this in the columns section (to action your second suggested method), I end up with two lots of the same columns and my table now loos like this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total On Time[/TD]
[TD]Total Late[/TD]
[TD]Total On Time[/TD]
[TD]Total Late[/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD]797[/TD]
[TD]336[/TD]
[TD]797[/TD]
[TD]336[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]142[/TD]
[TD]48[/TD]
[TD]142[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]216[/TD]
[TD]196[/TD]
[TD]216[/TD]
[TD]196[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]439[/TD]
[TD]92[/TD]
[TD]439[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Not sure what I am doing incorrectly?

 

[bobsan42]

Re: Pivot Table % Help

Let's start from the beginning - what does your raw data look like ? And what is your pivot table structure?