cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anchal5335
Helper I
Helper I

How to calculate average based on filters selected on the page?

Hi,

 

I have a created a page in power bi that shows count of items sold to different customers in a table visual. With that I also have two slicer visuals - One which shows the years and second which shows the months. So now I wanted to calculate average based on the options selected on slicer.

 

For eg- If I choose Jan, Feb ,March then I want the average count of itmes sold to customer A,B ,C etc in these three months. Whereas, if I select Jan, Feb,March,April then I want the average count of itmes sold to customer A,B ,C etc in these four months. So basically it should take the items sold in each month for each customer and then divide the total by no of months selected (which would be subject to change as it depends on what months we choose).

 

Is there any way to achieve this? Any help would be appreciated!

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @anchal5335,

 

Assume sample data looks like below: 

 

q2.PNG

 

You can create a measure: 

 

Measure = var countitem=CALCULATE(COUNT('Table1'[Item]),FILTER(ALLSELECTED('Table1'),'Table1'[Customer]=MAX('Table1'[Customer])))
var countmonth=COUNTROWS(FILTERS(Table1[Month]))
return
DIVIDE(countitem,countmonth)

 

q3.PNG

 

See attached pbix file. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-qiuyu-msft
Community Support
Community Support

Hi @anchal5335,

 

Assume sample data looks like below: 

 

q2.PNG

 

You can create a measure: 

 

Measure = var countitem=CALCULATE(COUNT('Table1'[Item]),FILTER(ALLSELECTED('Table1'),'Table1'[Customer]=MAX('Table1'[Customer])))
var countmonth=COUNTROWS(FILTERS(Table1[Month]))
return
DIVIDE(countitem,countmonth)

 

q3.PNG

 

See attached pbix file. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @v-qiuyu-msft,

 

Your solution was helpful but can you please explain me why you took max function? I am still unable to understand that step!

Thanks @v-qiuyu-msft! This was helful!

r_anselm0
Frequent Visitor

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors