PDA

View Full Version : Cumulative % in Pivot



umasriram2
08-12-2008, 05:08 AM
I've a Sales report and I've a Pivot report pulled out of it to show Sales by Customer descending. The running total in Pivot -Field Settings-Options helps in getting the cumulative Sales amount. But how do I get cumulative Sales %? Please help... Thanks in advance

mdmackillop
08-12-2008, 02:37 PM
Hi,
Welcome to VBAX.
A sample file would make your requirements easier to follow. You can post a file using Manage Attachments in the Go Advanced reply section
Regards
MD

umasriram2
08-13-2008, 01:46 AM
Hi, Thanks for the suggestion. I've attached a sample file wherein I've drawn Pivot from the Data tab. I've the Revenue #s indescending order, in % and as cumulative. What I'm trying to get is the Cumulative %.

Bob Phillips
08-13-2008, 02:30 AM
Add a column to the Data sheet with a formula of

=B2/SUM($B$2:$B$16)

and include that in the pivot, you can then accumulate that column.

umasriram2
08-13-2008, 03:26 AM
That looks like a nice suggestion if I have only a particular period data. I've several months/years data which might not support SUM($B$2:$B$16). Sorry that my example sheet isn't reflecting that. I just wanted to keep it simple.I was wondering if there is an option in Pivot to do this?

Bob Phillips
08-13-2008, 03:52 AM
Why would it not support it, you can use a dynamic range.

I don't think there is Pivot option.

Bob Phillips
08-13-2008, 04:42 AM
Surely, Month would be one of the headers in the pivot, and then the pivot would sum by month.

umasriram2
08-13-2008, 04:55 AM
But wont the formula that you suggested to insert in Data sheet have the denomiator with all months data added... In this example

Customer Month Amount cum
ABC 1 $12000 40% (12000/30000)
ABC 2 $18000 60% (18000/30000)

when I draw a Pivot, it would look like

Sum of cumMonth Customer12Grand TotalABC40%60%100%Grand Total40%60%100%

I want to see 100% for month 1 & 2 as $12000 & $18000 is the Total reveune(100%) for that month. Hope this clarifies

umasriram2
08-13-2008, 05:11 AM
But wont the formula you are suggesting to include in Data sheet have the denominator as all the months..In my example, if I use your formula in cum column

CustomerMonthAmountcumABC1$12,000 20%(12000/60000)ABC2$18,000 30%(18000/60000)DEF1$10,000 17%DEF2$20,000 33%


I would get the Pivot as

Sum of cumMonth Customer12Grand TotalABC20%30%50%DEF17%33%50%Grand Total37%63%100%

while I want to see

Sum of cumMonth Customer12ABC55%47%DEF45%53%Grand Total100%100%

Hope this clarifies.

umasriram2
08-14-2008, 08:32 AM
Please find the attached file and let me know your thoughts

mdmackillop
08-14-2008, 08:57 AM
Duplicate posts deleted.

Bob Phillips
08-14-2008, 09:02 AM
.

Jacob Hilderbrand
08-14-2008, 09:12 AM
Uma was having trouble posting, Here is the followup.

umasriram2
08-19-2008, 07:04 AM
As I mentioned it is not only months that I give as option in the Pivot but also quarter and year. Even if I've seperate columns for Cumulative % by year or quarter, I would need to change the layout in the Pivot to see the right numbers. Is there a more dynamic way to handle this? Please refer the excel file attached for clarity...thanks