<=0 at the end of Calculate

[Pillsbury33]

Hello everyone,

I'm trying to include less than or equal to zero at the end of this and can't make it work:

ZPs completed on time = CALCULATE(DISTINCTCOUNT(ZP[ID]),ZP[completed on time]<0)

Any ideas?

Cheers, Pilsbury33.

[az38]

Hi @Pillsbury33 

your statement looks ok, but it maybe affected with row-context.

try also

ZPs completed on time = CALCULATE(DISTINCTCOUNT(ZP[ID]),ALL(ZP),ZP[completed on time]<0)

[Pillsbury33]

Thanks @az38 

I tried this:

ZPs completed on time = CALCULATE(DISTINCTCOUNT(ZP[ID]),ALL(ZP),ZP[completed on time]<0)

But it returned a value I can't even work out where it has come from. Not the value I was expecting. I was expecting to see 6 and it returned 88. Any ideas?

The original statement does work, but it's only returning values of less than zero (i.e. a negative number) and not the zeros themselves.

Cheers, Pillsbury33.

[az38]

@Pillsbury33 

you also can try ALLSELECTED()

ZPs completed on time = CALCULATE(DISTINCTCOUNT(ZP[ID]),ALLSELECTED(ZP),ZP[completed on time]<0)

[Pillsbury33]

Thanks again, @az38 

Allselected returned the same result. I'll share my workings once I've removed everything that doesn't need to be shared. To make sure I understood correctly as well: should my first statement have been enough to include zero and all negative numbers, rather than just the negative numbers i.e. those that are less than zero but do not equal zero?

Cheers, Pillsbury33.

[az38]

@Pillsbury33 

< 0  means, it will be calculated IDs with negative value

<= 0  means, it will be calculated IDs with negative value and zero value

[Pillsbury33]

Hello @az38 

I did <=0 but it returned the the same value as All. I would guess this is because the column includes only includes negative values, zeros and blanks. Does the statement need to exclude the blanks? If so, what would this look like?

Thanks again, this is much appreciated.

[az38 · Accepted solution]

@Pillsbury33
here's the solution

ZP Completed on Time - DIVIDE(CALCULATE(DISTINCTCOUNT(ZP[ID]), ZP[Idea Status] - "Completed", ZP[Time Completed] < 1), CALCULATE(DISTINCTCOUNT(ZP[ID]), ZP[Idea Status] - "Completed") )

[az38]

@Pillsbury33 

I think you should better know how to calculate it, with or without zeros 🙂

I still work in the dark and can only imagine how does your data looks like.

it would be much more effective if you show data example

[az38]

@Pillsbury33 

it is about tow context. my statement gives you ALL [ID] where [completed on time]<0 all over the table. maybe you have additional row filtering.

show your data example to further analysis