Average needs to ignore zero and negative values

DAX DAX Calculations
1

Hello,

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

2

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.

3

Hello:
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

4

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)