Matrix show values only lower than average

[alexBL]

Hi All,

I have a matrix which is showing domain names from an email sending platform, with multiple brands which can be selected in a slicer. One of the values is open rate which is a %. I need to create something that only shows values lower than the average open rate when selecting a brand.

For example brand A has an average open rate of 15%, so when selecting brand A in the slicer I want the matrix to only show domains with an average open rate of lower than 15%.

Then when selecting brand B the average open rate is say 20% I then want the matrix to only show domains with a open rate of lower than 20%.

Is this possible?

Thanks in advance!

[v-yalanwu-msft]

Hi, @alexBL ,

Because I am not very clear about your data, If it not resolved, please provide me  with your pbix file after removing sensitive data.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[v-yalanwu-msft]

Hi @alexBL  ,  

You could create flag measures by the following formula: 

Flag =
IF (
    MAX ( [ open rate] )
        < CALCULATE ( AVERAGE ( [ open rate] ), ALLEXCEPT ( 'Table', 'Table'[brand] ) ),
    1)

Then apply it into filter :

The final output is shown below:  

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[alexBL]

Hi @v-yalanwu-msft 

Thanks very much for the reply. This seems to be very nearly what I need, but seems to miss some domains out.

The average open rate across all my brands is 17%; if I manually only show domains less than 17% on the open rate field in the matrix then these are my results:

When using the calculted colum you helped with these are the results:

You're method works becasue the values the matrix returns are below the average, but seems to miss out quite a few of the domains.