sum up to 0 excluded in average calculation in pivot table

[cheryl]

Hi,

I have a table like this:

(just an example, there will be more customer and more months.)

and I want to calculate the average amount for each month, and exclude the customer with amount sum up to 0.

so for the above example, for month Aug-18, the average value will be:

600/1=600 (the total amount / distinct number of customer with zero sum excluded), so I only account for B but ignore A since A sum up to zero amount.

And I want to show the average value for each month in a pivot table, what I did is:

Sum([Amount])/count(distinct{<[=aggr(sum(Amount ),Customer)]-={"0"}>} OrderDebtor)

But it doesn't work, It didn't ignore the customer with zero sum.

Can anybody help me with this?

Thanks a lot!

[chrismarlow · Accepted solution]

Hi,

Maybe try;

sum(aggr(if(sum(Amount)<>0,1,null()),Customer))

as denominator,

Cheers,

Chris.

[anat · Accepted solution]

use below expression as denominator:

=sum(aggr(if(sum(Amount)<>0,count(distinct Customer),null()),Customer))

[cheryl]

Problem solved!

Thanks very much Anat and Chrismarlow !