Exclude values in Average calculation

[mnb_dk]

Hi there

I have a matrix showing product sales and I would like to see the average unit cost for each product. The problem is that the average is incorrect since it includes lines where QTY = blank/0 and includes these in the average calculation. 

I can filter on the QTY visual to "Show items when the value is not blank". However it only works if I include the "Size" data point in the matrix and I would like not to show this, so I only have two levels in the matrix:

1. Product ID

2. Color

3. Size (I DONT WANT TO SEE THIS IN MY MATRIX)

I would expect to see two colors with an average of 138,36 each and also in total. 

Below data example:

Product	Color	Size	Unit costs	QTY
Product 1	Black	L/XL	138,36	46
Product 1	Black	M/L	138,36	88
Product 1	Black	S/M	138,36	104
Product 1	Black	XS/S	138,36	66
Product 1	Green	L	129,72
Product 1	Green	L/XL	138,36	71
Product 1	Green	M	129,72
Product 1	Green	M/L	138,36	179
Product 1	Green	S	129,72
Product 1	Green	S/M	138,36	216
Product 1	Green	XL	129,72
Product 1	Green	XS	129,72
Product 1	Green	XS/S	138,36	142
Product 1	Green	XXL	129,72

[v-yangliu-msft · Accepted solution]

Hi  @mnb_dk ,

Thanks for the reply from @HotChilli , please allow me to provide another insight: 

Here are the steps you can follow：

1. Create measure.

Measure =
AVERAGEX(
    FILTER(ALL('Table'),
    'Table'[Color]=MAX('Table'[Color])&&[QTY]<>BLANK()),[Unit costs])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[HotChilli]

Put it on 3rd party site (dropbox or similar) and post the link here

[HotChilli]

It will be sufficient.  If you use it in a pbix and post it, I'll see why my suggestions are not working for you.

[mnb_dk]

Ok I will try. But for some reason I can't upload files here?

[HotChilli]

You want to include qty > 0 , not exclude them.  Post your pbix and I'll have a look at it

[mnb_dk]

My pbix contains sensitive data, but the example data provided above should be sufficient?

[HotChilli]

You have a few alternatives:

1. Write your own average measure excluding the rows where qty is empty/0

or

2. Put a visual filter on the matrix to filter in the the qty values you want , for example, include only qty > 0

[mnb_dk]

Using the visual filter by excluding qty values >0 makes no different. Only if I keep the "Size" in the visual. If I remove it, it makes no difference.