

April 12, 2020

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

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

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
1 Guest(s)
