Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VikkiC
Regular Visitor

Average value for entire table with filters

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 🙂 

 

 

1 ACCEPTED SOLUTION
VikkiC
Regular Visitor

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!

 

View solution in original post

4 REPLIES 4
VikkiC
Regular Visitor

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
Regular Visitor

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.

VikkiC_0-1604675833461.png

 

 

@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
Super User
Super User

@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)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.