Average value for entire table with filters

[VikkiC]

Hi Everyone, 

Looking for some advice please. I am trying to create a column in my "opportunites" table that gives me the average of the entire table but with some filters.

I want it to Average the total value of opportunity rows that are of product type "Toolkit", are forecasted at "pipeline" and where the oportunity value is greater than 0. I am then planning on repeating this column for each of our other products. The end goal will then be to create a last column for each product that compares if the value of that opportunity is greater than the average and then finally show a dashboard that shows opportunities that are greater than the average for that product type.

Using the product Toolkit as an example the formula i have right now but is not filtering correctly is;

Average Toolkit = CALCULATE(SUMX(ALL(Opportunities),Opportunities[Value]),Opportunities[Type]="Toolkit",Opportunities[Forecast]="Pipeline",Opportunities[value]>0)

/CALCULATE(COUNTX(ALL(Opportunities),Opportunities[Value]),Opportunities[Type]="Toolkit",Opportunities[Forecast]="Pipeline",Opportunities[value]>0)

Thank you in advance 🙂 

[VikkiC · Accepted solution]

Thank you for the above @V-pazhen-msft The solution got me 98% of the way there by suggesting the use of "FILTER". In the end the formula that worked was;

CALCULATE(SUM(Opportunities[Value]),

FILTER(Opportunities,Opportunities[Type]="Toolkit"),

FILTER(Opportunities,Opportunities[Value]>0),

FILTER(Opportunities,Opportunities[Forecast]="Pipeline"))

/

CALCULATE(COUNTROWS(Opportunities),

FILTER(Opportunities,Opportunities[Value]>0),

FILTER(Opportunities,Opportunities[Forecast]="Pipeline"),

FILTER(Opportunities,Opportunities[Type]="Toolkit"))

Thanks again for the help!

[VikkiC]

Thanks at @amitchandak I am doing a column and i want the output to be the same value for every single row in the table so that i can compare to see if the value of the opportunty is greater than the average. The above gives me a different value for each row. I could do a measure but dont think that would work? 🤔

So ideally id like the average toolkit to column in this example to be 37969 all the way down.

[V-pazhen-msft]

@VikkiC 

I don't think you should use sumx and countx with all(table) if you create a columns. Try this:

CALCULATE(SUM(Opportunities[Value]),Filter('Opportunities',Opportunities[Type]="Toolkit",Opportunities[Forecast]="Pipeline",Opportunities[value]>0))

/
CALCULATE(COUNTROWS(Opportunities),Filter('Opportunities',Opportunities[Type]="Toolkit",Opportunities[Forecast]="Pipeline",Opportunities[value]>0))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@VikkiC , is this a column or measure. If measure you can not use it in the column.

If it is column then try like

Average Toolkit = CALCULATE(SUMX((Opportunities),Opportunities[Value]),Opportunities[Type]="Toolkit",Opportunities[Forecast]="Pipeline",Opportunities[value]>0)
/CALCULATE(COUNTX((Opportunities),Opportunities[Value]),Opportunities[Type]="Toolkit",Opportunities[Forecast]="Pipeline",Opportunities[value]>0)