Count Distinct in pivot table?

[Solo4357]

I have a report that I'm aggregating by PM, Ctrl Job, and Job number by row, and Year, Qtr, Month by column... looks like this:

Jan Feb

PM Cost Billed Cost Billed
Bob 500 1000 200 400
CtrJobNum1 100 200 100 200
Jobnum1a 50 100 100 200
JobNum1b 50 100 0 0
CtrlJobNum2 400 800 100 200
JobNum2a 400 800 100 200
Jim
CtrlJobNum3


I'm trying to get a count of JobNumber (2nd indent in) but since there are many duplicates in the raw data, I get wrong counts. I need to count distinct. Any ideas on that? I can get a good count from the database if I remove the post date but I need the date so I can do the filtering/grouping by time periods. So I'm not sure If I can do it through the initial query from the db or in the dataconnection in excel or a calculated field in the pivot table. I'm willing to do whichever.

 

[SkipVought]

You could sort your source data by PM, Ctrl Job, and Job number and then insert a formula in the adjacent column to the right that if all the sequencers are equal to the the values in the previous row then ZERO else ONE.

Then SUM this new column in your PT.

BTW, in the Data Range Properties of your QueryTable, check the box at the bottom.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[combo]

In some cases (data size) you can use pivot table to create distinct rows. Drag all necessary fields as row fields, any one field for aggregation. Remove subtotals from each field and table. The output, copied, can be used as a source for the final table.

combo