-
1. Re: how to find how many zero's in my measure
Andrew Kim Sep 21, 2017 10:58 AM (in response to mahesh p)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
mahesh p Sep 21, 2017 11:01 AM (in response to Andrew Kim)not working
-
3. Re: how to find how many zero's in my measure
Ray Givler Sep 21, 2017 11:03 AM (in response to mahesh p)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
Andrew Kim Sep 21, 2017 11:04 AM (in response to mahesh p)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
Michael YeSep 21, 2017 11:04 AM (in response to mahesh p)
3 of 3 people found this helpfulMahesh,
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
-
6. Re: how to find how many zero's in my measure
Michael YeSep 21, 2017 11:07 AM (in response to Ray Givler)
3 of 3 people found this helpfulRay,
You cannot use countd(), otherwise, no matter how many zero's, it always show 1. You should use Count().
Thanks,
Michael
-
7. Re: how to find how many zero's in my measure
Ray Givler Sep 21, 2017 11:14 AM (in response to Michael Ye)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
Michael YeSep 21, 2017 11:27 AM (in response to Ray Givler)
4 of 4 people found this helpfulRay,
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
-
9. Re: how to find how many zero's in my measure
Ray Givler Sep 21, 2017 11:34 AM (in response to Michael Ye)Okay, I was assuming unique name, but I concede the point.
-
10. Re: how to find how many zero's in my measure
mahesh p Feb 22, 2018 3:41 AM (in response to Michael Ye)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
Michael YeFeb 22, 2018 6:58 AM (in response to mahesh p)
3 of 3 people found this helpfulMashesh,
make a calculated field:
Count of Zero:
Count(IF [Sales]=0 THEN 1 END)
and put it in text, you will get it.