Forum Discussion
Exclude values in Average calculation
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 |
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
8 Replies
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
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.
You want to include qty > 0 , not exclude them. Post your pbix and I'll have a look at it
My pbix contains sensitive data, but the example data provided above should be sufficient?
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.
Ok I will try. But for some reason I can't upload files here?
Put it on 3rd party site (dropbox or similar) and post the link here
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
