Counting Unique - Non Null Values in a cross table.

[Ryan James 3]

I am have monthly data that I am trying to count non - null unique values. But I keep getting the wrong results.

Here is an an example:

I have the following data:

Well Month Volume

A 1 12

A 2 10

A 3 11

B 1 0

B 2 0

B 3 0

I am try to get the following result:

Total Wells Wells Greater than Zero

2 1

This is my formula

Unique Count ([Wells]) as [Total Wells],

Unique Count(CASE WHEN sum([Volume]) > 0 THEN [Well] ELSE NULL END) AS [Wells Gretter than Zero]

However I get two in both columns. What am I doing wrong?

Thanks,

[Gaia Paolini]

This would work as a calculated column in a table:

Sum(Integer(sum([Volume]) over ([Well])>0)) / Count(RowId()) over ([Well])

where

sum([Volume]) over ([Well])>0 is True for well A and False for well B

Integer(of the above) gives you 1 for True and 0 for False

Sum(of the above) gives you 3 for 1 and 0 for 0

Count(RowId()) over ([Well]) normalizes the count to 1