Filtering on measure aggregates

[JV_PBI]

Any help would ebe appreciated - new to Power BI.  I created a visual table from an excel table that basically aggregates and averages data.  That part works fine.  Now that I have averages for all category, I want to be able to filter at the category average level and then recalculate a new average of all the underlying rows. I tried to filter in the visual but it either doesn't allow it for example the value of average has inactive filter or if I use the row data from the orginal table it filters out the rows before doing the average calculation.  I want to filter at the average value.  Thanks. 

[v-cazheng-msft · Accepted solution]

Hi @JV_PBI 

For your question, I hope the following answers can be solved:

You can create a measure to get the result you desired.

AverageValueLower8.00 = AVERAGEX(VALUES('Table1'[Model]),IF([Measure(Average Value1)]<8.00,[Measure(Average Value1)],BLANK()))

The result looks like this:

Best Regards,

Caiyun Zheng

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

[v-cazheng-msft · Accepted solution]

Hi, @JV_PBI 

IF using full expression Average(Model) in IF statement, due to the context used to calculate, the “Total” row may be wrong.

Using full expression in If statement like this:

AverageValueLower8.00-1 =

var averag_model=AVERAGE(Table1[Value1])

return AVERAGEX(VALUES('Table1'[Model]),IF(averag_model<8.00,averag_model))

The result looks like this:

The “Total” row is wrong, then you can refer Dealing with Measure Totals to cope with it.

Best Regards

Caiyun Zheng

[Ashish_Mathur]

Hi,

Try these measures

Average value = AVERAGE(Data[Value1])

Measure = AVERAGEX(FILTER(VALUES(Data[Model]),[Average value]<8),[Average value])

Hope this helps.

[v-cazheng-msft · Accepted solution]

Hi @JV_PBI 

For your question, I hope the following answers can be solved:

You can create a measure to get the result you desired.

AverageValueLower8.00 = AVERAGEX(VALUES('Table1'[Model]),IF([Measure(Average Value1)]<8.00,[Measure(Average Value1)],BLANK()))

The result looks like this:

Best Regards,

Caiyun Zheng

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

[JV_PBI]

 I agree this expression gives the correct answer.  Can you explain why a measure has to be used in the IF statement vs the full expression Average(Model).  I tried both ways and it only works with the measure. Thanks

[amitchandak]

@JV_PBI , Create a measure like. Or replace the your measure with calculation in given measire

averageX(filter(Values(Table[Model]),[measure(Average Value1)] <8.00),[measure(Average Value1)])

[JV_PBI]

Thanks.  I tried your measure and got some interesting results.  I added the new measure to the table and while the average for Model A is blank, which is correct, the summary average at the bottom of the column is still showing the average for A, B, and C.  

[amitchandak]

@JV_PBI , Model is missing in values

averageX(filter(Values(Table[Model]),[measure(Average Value1)] <8.00),[measure(Average Value1)])

A will blank because it is not less 8. If there are 2 measures you will get all model that two measure covers