11 Replies Latest reply on Feb 22, 2018 6:58 AM by Michael Ye

# how to find how many zero's in my measure

Hi

am having to columns like

Name
Sales

A

78
B0
C45
D0
E784
F441

now i need to find how many Zeros in my Sales measure, so how to show this?

• ###### 1. Re: how to find how many zero's in my measure

try this:

IF SUM([Sales]) = 0 THEN 1

ELSE 0

END

That should give you a count of all of the 0's

• ###### 2. Re: how to find how many zero's in my measure

not working

• ###### 3. Re: how to find how many zero's in my measure

Or, depending on what you are trying to do:

countd(if [Sales]= 0 then [Name] end)

• ###### 4. Re: how to find how many zero's in my measure

Couple questions,

What's the error you're seeing?

What dimensions are on your worksheet?

What should the final worksheet look like?

• ###### 5. Re: how to find how many zero's in my measure

Mahesh,

make a calculated field:

Count of Zero: Count(IF [Sales]=0 THEN 1 END)

and put it in text, you will get it.

Thanks,

Michael

3 of 3 people found this helpful
• ###### 6. Re: how to find how many zero's in my measure

Ray,

You cannot use countd(), otherwise, no matter how many zero's, it always show 1. You should use Count().

Thanks,

Michael

3 of 3 people found this helpful
• ###### 7. Re: how to find how many zero's in my measure

I was counting distinct Names with 0 Sales, not distinct Sales with 0 Sales...

• ###### 8. Re: how to find how many zero's in my measure

Ray,

Using countd(Name), if there are several records for Name A with several zero sales, it will be wrong.

The sample data is not stored in a standard table because there is no primary key(PK). For a relational database table, there should be a PK with Order ID(or Record ID, something like that), then it is possible and reasonable for a same name with several 0 sales records. Therefore, countd(name) may lead to a wrong result.

Michael

4 of 4 people found this helpful
• ###### 9. Re: how to find how many zero's in my measure

Okay, I was assuming unique name, but I concede the point.

• ###### 10. Re: how to find how many zero's in my measure

so how can I find how many zeros in that sales, in this case, it will be 2 can you help in this.

• ###### 11. Re: how to find how many zero's in my measure

Mashesh,

make a calculated field:

Count of Zero:

Count(IF [Sales]=0 THEN 1 END)

and put it in text, you will get it.

3 of 3 people found this helpful