calcuate % using only subtotal data in a pivot table

[itprsn]

Data looks like the attached:

product order press printed overrun waste %
newspaper1 10,000 4 5,760
newspaper1 10,000 5 5,840
newspaper1 10,000 n/a n/a 1,600
Summary 10,000 All 11,600 1,600 xx.xx


What I need to do is calculate a waste % based on the information that is found summarized on the subtotal line. Any suggestions as to how I can do this? Thanks...

 

[SkipVought]

Hi,

Your % column...
[tt]
=IF(E2>0,E2/SUMIF(order,B2,printed),0)
[/tt]
using named ranges.

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[itprsn]

Not sure I understand how this works in pivot table?

 

[SkipVought]

[tt]
Data
order Sum of printed Sum of overrun Sum of waste%
10,000 11600 1600 14%
Grand Total 11600 1600 14%
[/tt]

Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[itprsn]

Let me try and clarify: in SQL I'm pulling several records for each product's press runs and using a union to pull in one additional record for the total of all press runs for that product (to get the overrun I'm looking at all the press runs in total, then comparing to the order). All's good so far. Then I pull the detail records and the summary record into a pivot table and subtotal all the data so I have it all on one line. Then what I'm attempting to do is to create a new field and get the waste % to print in that new field on the summary/subtotal line. So far my new field is only printing on the detail lines, not the summary/subtotal lines where I need it to be... Thanks...

 

[SkipVought]

Works for me when you INCLUDE the formula in a adjacent column in the source data query table AND make sure the Data > Import External Data > Data Range Properties has the box at the bottom checked. THEN, use this data in your PT. Pretty simple!

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[itprsn]

Got it, adding an adjacent column in the data source was new to me but I understand where you're going with this now... Thanks for your help!!