Forum Discussion

mnb_dk's avatar
mnb_dk
Helper I
2 years ago
Solved

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 = bla...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
HotChilli
Community Champion
2 years ago

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's avatar
mnb_dk
Helper I
2 years ago

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.