Forum

Adding a column to ...
 
Notifications
Clear all

Adding a column to a pivot table that includes a specific calculation

5 Posts
2 Users
0 Reactions
80 Views
(@robm)
Posts: 9
Active Member
Topic starter
 

Scratching my head a bit and hoping that someone might be able to point me in the right direction... not sure if its possible.

Trying to analyse sales figures and have got a data source of Invoice numbers, Sale item description and £ sale value

Have created my pivot table and set the grouping as I want and set up my slicer

So my pivot table has 1st column Sale Item description - sub-divided into 3 cost/sale centres -  each group is subdivided into 5 different types of sales from those 3 cost centres and lastly - this group is subdivided by it's original invoice number

The 2nd column is the sales value 

I wanted to add a 3rd column that calculates the value of Group 1 sales as a percentages of grand Total, But when group 1 is opened to show the subdivisions, I want to see the value of the sales in each subdivision expressed as a percentage. 

I can do this easy enough by using a formula alongside the table, but as soon as you use the filter, the table changes size and you're left with a column of odd numbers or #Ref1 that makes no sense.

Hope this is clear as mud! Confused

Thanks

Rob

 
Posted : 05/07/2020 8:49 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Rob, 

You lost me at "So my pivot table has 1st column Sale Item description - sub-divided into 3 cost/sale centres -  each group is subdivided into 5 different types of sales from those 3 cost centres and lastly - this group is subdivided by it's original invoice number" because you initially said your data source only has 3 columns: "have got a data source of Invoice numbers, Sale item description and £ sale value"...that's 3 columns, so I'm confused about how you're sub-dividing and grouping by other criteria.

Please upload a sample Excel file with your data, Power Pivot model and desired result for the PivotTable. This will ensure we understand the complete picture and get the answer right first time.

Thanks,

Mynda

 
Posted : 06/07/2020 1:08 am
(@robm)
Posts: 9
Active Member
Topic starter
 

Hi Mynda

As I was preparing a sample draft file to upload - I think the answer occurred to me - I needed to put the calculations into the data table before creating a pivot table..

 

I attach the sample file which shows what I'm trying to do - essentially I want to see the percentages of each type of sale, when I'm using the filter... I think this works now and can be applied to the real file.

Thanks for the help

Rob

 
Posted : 06/07/2020 5:02 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Rob,

Thanks for sharing the file. There is an easier way that will be more efficient for Excel to calculate:

1. Add the Value field to the PivotTable again

2. Right-click on one of the value cells in the PivotTable > Show Values as > Percentage of Grand Total.

Mynda

 
Posted : 06/07/2020 7:18 pm
(@robm)
Posts: 9
Active Member
Topic starter
 

That's great, Many thanks indeed.

rob

 
Posted : 07/07/2020 4:33 am
Share: