Using subtotals in Pivot table formulas

[ninfanger]

The set up:


Our legacy database provides 30 different report types that our
clients can run from our website.
I have a spreadsheet listing every report run by customers over the
past 2 years; including unique ID#, Client name, report name, User
name, Year, Date, Time, etc.


The Pivot Table:
My pivot table is set up as follows:
- ClientName, ReportName, and Username as Row labels
- Year as Column Label
- Count of ClientName as value
- Count of ID# shown as "% of column" as another value
The pivot lists ClientName in column A, ReportName in column B,
Username in C and the count of ClientName for each report type in
Column D. With Subtotals for each ClientName.
The 2nd value described above is in column E (based on Count of ID#
and shown as % of column). This gives me the % of the grand total for
each line item.
Finally my question:
Without moving the ClientName from Row Labels to Column Labels, how
can I get a % of the Subtotal for each ReportName? I have tried
unsuccessfully to create a calculated field and my gut tells me the
answer is there. I just haven't found a workable formula.
Suggestions?

[Kimberly]

can I get a % of the Subtotal for each ReportName?

Sorry, I am lost. You want a percent of what for each ReportName?

[ninfanger]

A picture is worth????

The attached spreadsheet [reports run 4.xlsx] shows the pivot results for 3 of many clients. It shows; (A) Client Name, (B) which reports that client ran, (C) how many of each report were run, and (D) the % of the entire report. (Client #2 then ran 2.69% of all reports included in the pivot.)

I manually created Column E to show what % each report type is of the related Client's total reports. (ie: 37% of Client #1's reports are Report C.) I want the Pivot table to generate Column E.

I think the answer is a Calculated Field that counts the instances of each report type for a client and devides that by the total count of reports for that client.

Hopefully that is less obfuscated.
And truly thank you for any help you can offer.

[Kimberly]

Leave Job name as a row field, but also add Job Name as a data field ('Count of Job Name', actually)
Set it to display as % of Parent Total, with the base of Client Code.