How to only see the Zero Totals

[#1]

Dear All,

Looking for some guidance on the following

Name               Points

Person 1          0

Person 2          10

Person 3          0

Person 4          5

I'm trying to achieve a list of 'Names' where the points is zero.

Name               Points

Person 1          0

Person 3          0

I am using the following expression

Sum (if ([Grand Total] = 0,[Grand Total]))

In the Presentation Tab, if I have "Suppress Zero-Values" selected Nothing shows on my chart which makes sense as all my values are zero.

If I uncheck "Supress Zero-Values" then I get

Person 1     0 ** Correct

Person 2     0 ** Incorrect

Person 3     0 ** Correct

Person 4     0 ** Incorrect

Am thinking this is a rookie mistake but don't know how to fix it.

Any assistance appreciated.

Regards,

Jim

[swuehl · Accepted solution]

Sum (if ([Grand Total] = 0,[Grand Total]))

If Grand Total not equals zero, the else branch of your if statement should be executed. There is no else branch, so NULL is returned. A sum of NULL is zero.

To resolve your issue, try this as expression and uncheck 'supress zero values' in presentation tab:

=Sum ( {<Name = {"=sum([Grand Total])=0"} >} [Grand Total])

'Name' being your Person field name.

[#3]

Many thanks.

Being new to sets can you please explain how to read the solution?

[#4]

Not the 'correct' answer I marked below.  I cheated and solved it this way.

Sum (if ([Grand Total] = 0,-1,0))

Basically I set the zero values (which I wanted to see)  to -1 and the non zero values (which I wanted to hide) to 0.

Then I marked the Supress Zero-Values checkbox to checked on the presentation tab

Then I hid the expression column (still on the presentations tab)

I just get then a list of names rather than a list of names and a column with zeros, which might be more correct than what I was originally trying to achieve.