Pivot help - calculated field
experts,
I have a query as a Pivot Table view.
I used the button "Auto Calc"
It summs as expected.
I need to add another column that shows the summ as a percent of the overall total.
I can change the Auto Calc sum to "show as percent of Grand Total" but I also lose the subtotals.
How can I add another column that will show the percent of the overall total but keep the subtotals?
I have a query as a Pivot Table view.
I used the button "Auto Calc"
It summs as expected.
I need to add another column that shows the summ as a percent of the overall total.
I can change the Auto Calc sum to "show as percent of Grand Total" but I also lose the subtotals.
How can I add another column that will show the percent of the overall total but keep the subtotals?
Add a second, duplicate Amount field in your query (definitely named), and drag that into the "Drop Totals of Detail Fields Here", and change that one to %s.
ASKER
Philip: that seems to be pretty close. I drag the [Amount] into the query as a duplicate field and gave it a name then I show the field list and drag this new duplicative field into the pivot and I can show it as Percent of Grand Total but the [Amount] is duplicated. I have 2 columns that show [Amount] but in the duplicative column I do have a percent but just need to remove the duplicate [amount]. Not sure if you follow.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you Philip. Have it working now...