This discussion is archived
5 Replies Latest reply: Sep 28, 2012 10:26 AM by David_T

PIVOT Table Sub-totals

Currently Being Moderated

I have a simple question on calculating sub-totals in a PIVOT table. Here is my requirement -

01 Expenses Item1 \$25.00
Item2 \$35.00

02 Revenue Item1 \$10.00
Item2 \$15.00
Item3 \$37.00

03 Goods Item1 \$12.00
Item2 \$11.00
Item3 \$45.00

Need to calculate Sub totals for
1) Expenses , Revenue and Goods - This is simple, I use the Calculate filed and I am able to find the Subtotals.
2) Have to find Subtotals for Expenses Subtotal + Revenue Subtotal AND also Subtotals for Revenue Subtotal + Goods Subtotal..

Thanks,
Sam
• 1. Re: PIVOT Table Sub-totals
Currently Being Moderated
Create a BIN for each category and go for totals.

Pls mark if helps
• 2. Re: PIVOT Table Sub-totals
Currently Being Moderated
I tried with BINS, still not showing the correct values..
• 3. Re: PIVOT Table Sub-totals
Currently Being Moderated
Something wrong in your bin. You need to pull one more column and top of that use bin and then total
more or less should be
case WHEN col IN ('Expenses', 'Revenue') then '1' WHEN col IN ('Revenue', 'Goods') then '2' end

pls mark if helps
• 4. Re: PIVOT Table Sub-totals
Currently Being Moderated
The Bins are working correctly, however when I include Expense, Revenue together all the associated line items are also showing up. I do not want the individual line items to show up, instead the Amt Subtotals. Is there a way to supress the individual line items ?
• 5. Re: PIVOT Table Sub-totals
Currently Being Moderated
951353 wrote:
The Bins are working correctly, however when I include Expense, Revenue together all the associated line items are also showing up. I do not want the individual line items to show up, instead the Amt Subtotals. Is there a way to supress the individual line items ?
Once you create the BINS column, take the original column (that housed the type e.g., Expense, Revenue, etc.) out of your query. Put that in the Excluded Section of your pivot table.

Legend

• Correct Answers - 10 points