cumulative percentage

Hi, I have assigned 100 accounts to my agent in beginning of the month.
He worked upon 10 accounts on day 1, 2 accounts in day 2, 5 accounts in day 3 and the total accounts worked by him were 72 in the month.

I want to make a pivot table that shows his daily performance.
i.e. on day one his work percentage was 10% (10/100), on day 2 his work percentage was 12% (10+2)/100 and so on.
attached is the raw sheet that I am using to make pivot table.Sample-data.xlsx
VasuSidhuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arno KosterCommented:
Somehting like this?
Sample-data2.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VasuSidhuAuthor Commented:
Yes. but the cumulative result is not coming as per the result required. can you explain me what have you done so that I can imitate it in my work book
VasuSidhuAuthor Commented:
the base should always be count of accounts allocated on a given date and the numerator will be the cumulative sum of accounts worked.
as in given example A was assigned 27 accounts on may 1. he worked on 3 accounts on may 1 so his work% on may 1 = 3/27. similarly on may 2 he worked on 2 accounts so his cumulative work % = (3+2)/27
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Rob HensonFinance AnalystCommented:
You can replicate the table of calculations that you already had but with variable entries:

Count of jobs assigned on 1 May:
Formula in H6 =COUNTIFS($A:$A,$G6,$C:$C,$H$3)  copy down to H7 and further if required with more agents

Formula in I6 =COUNTIFS($A:$A,$G6,$D:$D,"<="&I$5)/$H6 copy right and down as far as required

Formula in O6 =COUNTIFS($A:$A,$G6,$D:$D,"<="&I$5) copy right and down as far as required, similar to above but without the division by total assigned for converting to percentage.

Thanks
Rob

File attached.
Sample-data.xlsx
VasuSidhuAuthor Commented:
Hi Rob,

I am dealing with complex database and the work% is only one matrix. this is the reason I am trying to work on pivot tables rather than formula calculated sheet.

though I have never worked on power pivots, I guess, there should be a solution in power pivots.

My preference is to get a solution in pivot tables but if not possible, any solution provided in power pivot is highly appreciated.
Rob HensonFinance AnalystCommented:
Ok, working it to see if I can get with Pivot.
Rob HensonFinance AnalystCommented:
Now as pivot.

Added extra columns to data and used those in pivot.
Sample-data.xlsx
Arno KosterCommented:
VasuSidhu,

I used pivot tables only, used column "agent name" as row label, column "account number" as value (count) and column "work date" as column label.

This is the base for the pivot tables "regular" and "percentage", the latter contains the same information but values are displayed in percentage of row totals rather that as is.

The "cumulative" pivot tables are identical to the other pivot tables but the values are displayed as running total and percentage running total.
Arno KosterCommented:
In my opinion, this is as far as it goes without inserting additional columns as suggested by Rob Henson.
Note though that the percentages displayed are relative to the row totals and indeed not relative to the total number of work assigned.
Rob HensonFinance AnalystCommented:
With additional columns, I added the Assigned quantity, cumulative count and cumulative percentage and then set the row values as a percentage of Assigned. I used the Assigned as a Row label rather than data value so that it appeared at the left hand side and didn't get split out by the date columns.
VasuSidhuAuthor Commented:
Thank you Rob and Akoster.

since the data is very huge and I would like to see the data in various types. i have simply changed the blank work dates into zero and then took the accounts_assigned_date into running total in work_date.

Sample-data.xlsx

regards,
Ranvir
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.