Average without zero values

[Anonymous]

Hi, the question is probably simple, but ...

There are workers and the number of transactions they have сommitted, and there are departments where they work. I need to make the average number of transactions for the department, this is not difficult to do. I do it like this:

Sum (count of transactions) / count (distinct (Worker)))

And everything works, but not the way i need to. I do not want to take into  my "average" workers who have't committed any transaction at all. I do not understand where I have to set an exception..

I have found a lot in a forum, but it does not work.

I will be very grateful for any help.

[ahaahaaha]

Hi,

Which flag in data  of workers who have't committed any transaction at all. May be fragment of source data and waiting result?

Regards,

Andrey

[JustinDallas]

Hello Mike,

I tried to get you a solution, but I couldn't figure one out.  Somewhere in this statement, probably in the Count clause, you have to have

IF(Aggr(Sum(TransactionCount), WorkerId) > 0)

But I'm not sure if that's the correct statement and  where it should go off the top of my head.

[TKendrick20 · Accepted solution]

Have you tried set analysis? Something like:

COUNT({<[Transactions]-={"0"}>} [Transactions]) / COUNT({<[Transactions]-={"0"}>} DISTINCT [Worker])

or

IF([Transactions]>0,COUNT([Transactions]) / COUNT(DISTINCT [Worker]))

[Anonymous]

Thank you!

It's really helpful!!