Average needs to ignore zero and negative values


Learning a lot on your webinars. Thanks.

I am trying to compute a calculated column in my table Order_Header for Average Sales (not returns) by Client.
My Order_Detail table has [$Sales] which has both positive and negative values. Negative values represent Returns.

The DAX formula below takes all [$Sales] positive and negative. I need to filter for only positive [$Sales] records.

$AvgSlsOnlyNoReturns = 
calculate(Average(Order_Details[$Sales]), USERELATIONSHIP(Order_Header[ClientID], OrderDetail[ClientID]))

…but do not know how I can eliminate “Zeros” of “Negative Values” in [$Sales] to compute correct average.

Appreciate your help and thanks in advance.

Best regards…Adi

Any reason why you are doing this in a calculated column?

I don’t really recommend this. This is something that should be done in measures as it’s far more efficient to do it this way from a performance, speed and re-usability perspective.

The use of USERELATIONSHIP seems very odd and unnecessary to me…

If you are unable to work it out as a measure I suggest added a demo file here of the exact scenario. That would help to fully understand the situation.

Many thanks for the quick response.
I have used the measure but I do not know how to use it in the next step of analysis.
The actual case is a bit more complex.
I am preparing appropriate dummy data to send it to you shortly for you advise.
Thanks and best regards…Adi

The outline of this scenario doesn’t seem to complicated so I’m surprised that it is.

I think if you have the right model setup then what you’re describing shouldn’t be too difficult in a measure (likely an iterating function like AVERAGEX)