Pivot Table - Count distinct values within a group

Posted by Daniel on September 25, 2001 2:00 AM

Hi,
Is there a way to count the distinct occurrancies of a value within a group? For example how many different customers. Here's some some sample data:

Customer Period Amount
A 1 10
A 1 12
A 3 11
B 1 15
B 1 11
B 2 13
B 3 14
C 3 10

Pivot Table:

Customer (All)

Sum of Amount
Period Total # of Customers
1 48 2 &LT;=&LT;br>2 13 1 &LT;=&LT;br>3 35 3 &LT;=&LT;br>Grand Total 96 3 &LT;=&LT;p>Is there a way to create a formula/calculated field to get the # of customers as shown above?

Any ideas are greatly appreciated.
Daniel

Posted by Mark W. on September 25, 2001 7:04 AM

See 30837.html (nt)

Posted by Daniel on September 26, 2001 12:30 AM

Re: Thanks but it doesn't work

Thanks Mark. Interesting idea, but it doesn't work in my case. It correctly counts the number of distinct customers in the grand total. But if I make the period a row field, then I get wierd results. I think this cannot be done on the data level, since the result always depends on the grouping. The way to do this in SQL terms, would be to COUNT(DISTINCT FieldName) for whatever records are part of the current grouping. For example, if I double-click on a data field, then Excel displays the detail records in a separate sheet. A COUNT(DISTINCT FieldName)) against these detail records would always return the correct result, no matter how the grouping is done. Unfortunately, I don't see how to do this. The most logical place would be in the options for a data field.

Any other ideas?

Posted by Mark W. on September 26, 2001 4:13 PM

Unfortunately...

...there's nothing comparable to COUNT(DISTINCT x)
for PivotTables.