Aggregation of Data in Pivot Table

[merp_393]

Hi Everyone,

I have been following Chandoo.org since many years and usually I get my answers through the FAQ's or some previous post but ultimately had to create an account to ask for help on a problem I am facing in Pivot table.

I have attached my sample data and the basic requirement what I have in the file. I have tried all the value field settings and creating calculated fields to achieve this but to no avail. Observed that the calculated field doesn't accept the SUBTOTAL() formula.
What I need is a "group by" and "subtotal" functionality on certain columns to analyze my data better.

Request for guidance on this issue if it can be solved via a pivot table or if there is any other way to achieve this?

Thanks a ton!

 

[vletm]

Which of those is valid result?

 

[merp_393]

Hi vletm, if you see the "Data" sheet, I have added the SUBTOTAL(9,) to sum the values in columns H - which I am referencing in the formula to calculate in column J.
So, when I filter the data on any column the formula in column J references the filtered data -> updates the subtotals -> calculates the formula based on the filtered dataset.
However, in the Pivot this is not the result. Subtotal is not being respected.

In the tab "Requirement", the first 2 pictures show the spreadsheet where I have filtered on column A and it updates the column J values based on the filter applied. Same results should be expected in the Pivot table wherein I have applied the same filters but it doesn't match.

Hope, I am able to explain. If not, please let me know I will update the file and attach providing screenshots of the expectation.

 

[vletm]

Data-sheet cell J3
=I3/SUMIFS(H$3:H$18,A$3:A$18,A3) and copy down
or
=I$3:I$18/SUMIFS(H$3:H$18,A$3:A$18,A$3:A$18)

 

[merp_393]

Hi, thanks for your suggestion. I agree that the SUMIFS logic will work if I just wanted to have the filter applied on just the Column A (business unit level). However, this would fail (give wrong results) if I apply the filter on some other column with the same formula (criteria being applied on just the column A).

SUMIFS references the static criteria (in this case, it is column A), but what I want is the criteria should be dynamic based on the user selection and that's why I was trying to accomplish this in a Pivot where the user can play around with the filters based on their requirement to see the results.

 

[vletm]

Did You mention somewhere about different 'filtering-cases'?
Here a sample of 'dynamic'-'filtering' with SUMIFS
by selecting ONE cells from 2nd row ( columns: A...H) to get those results.
... without any specific filtering.

 

[merp_393]

Hi vletm, thanks a lot - this helps! and my apologies for not being clear in my initial post.
I am able to update the VBA script to my actual data and its working. Although because my data is in 100,000+ line items it's taking time to solve or giving me runtime errors - which I am checking on.

I wanted to check with you if we can select multiple cells in the 2nd row across columns A to H - Is there a way to do that?

 

[vletm]

... hmm?
As well as, You've given an image that there are about 16 rows data ...
Then any sample solution could do based that idea ...
Have You ever asked to run 100m?
... but ... just one minute before start ... You'll figure that it will be 10kms. Do it matter?
I can test this only and only with Your data.

I can check something after I could know - what do You really need?
... eg ...
if You can select multiple cells ... Error % Contribution ... just will be smaller and smaller ... or how?
Would it give something - which could use somewhere?
... or is it something like nice to have?

 

[vletm]

While waiting Your replies ...
I tested something with Pivot-table + plus ... without VBA.
as well as
There is a new Do It-version.
Many things depends - what do You really need?