Log In
Register
Home
April 12, 2020
Offline
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! 
Thanks
Rob
July 16, 2010
Offline
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
April 12, 2020
Offline
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
July 16, 2010
Offline
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
1 Guest(s)
