How to filter rows greater than the average?

[Anonymous]

In Power BI Desktop, I have a measure: 'Measure_Average' which computes the average of all values in the column: [Amount] in the table: 'Billing'. My question is: how do I create a table visual and set the visual level filter to only those rows in the 'Billing' table whose [Amount] value is greater than the average as caculated by the measure: 'Measure_Average'?

My intent is to only show those rows in the Billing table whose Amounts are greater than the average.

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create column Average to get a fixed value, and create measure Filter1 ,then put measure Filter1 in the Visual Level Filter of table visual displaying the [Amount] , and setting the Filter1 as "is not blank".

Average = AVERAGE(Billing[Amount])

Filter1 = IF(MAX(Billing[Amount])>MAX(Billing[Average]),1,BLANK())

Best Regards,

Amy

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

[Anonymous]

Thank you for your response. That was a very good solution that you suggested @v-xicai .

While the suggested solution is working at the Visual level, my main goal was to make it work at the report level. Or, the other alternative would be to set this on a Slider visual to filter all the visuals on the report, for example: to show only those 'Locations'[Location Name] in a Slider visual whose sales have exceeded the average sales.

Kindly let me know how to do this since I am unable to set the Measure as a visual filter for a Slider visual, or set the Measure as a filter at the Report Filter or Page Filter level.

[v-xicai]

Hi @Anonymous ,

You are right, a measure can't be add to Page Level Filter or Report level Filter, while a column can. So you can change Filter1 from measure to column like DAX below, and drag it to Page Level Filter or Report level Filter, setting Filter1 as "is not blank".

Filter1 = IF(Billing[Amount]>Billing[Average],1,BLANK())

Best Regards,

Amy

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

[bvilten]

If you want to show the averages then you won't need the measure. Just drag a column Company (Just assuming on my part) into a table display then drag the Amount column in. Change the display of Amount to Average. Then set the visual filter to "is greater than".