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

Average with filters

Hi Guys,

 

I am struggling with a problem here. Let's see if someone can help.

 

I have a column where I have the time spent to solve a problem. Then I have other columns such as, type of problem, type of subject, type of student. etc over time (if problem 2 starts at day 3, at day 4 it will take 1 day, at day 5 it will take 2 days... until it closes at day 8 so it took 5 day s to solve it)

I need a measure that gives me the average of the time that takes a problem to be solved BUT taking into account only the maximum value for each problem. For example, for problem 2 it will take into account 5 days, not the 1-2-3-4, BUT when I filter by date into weeks, if the problem has taken for example 13 days (started a wednesday so in week 1 it took 4 days, in week 2 it took 11 days and in week 3 it took the 13 days) i need to have these maximum numbers for each week. Also I need the measure to take into account the different filters like type of problem, type of subject, etc.

Working with the quick measures, I've been able to accomplish kinda something that works only sometimes:

AVERAGEX(
	KEEPFILTERS(VALUES('Problem'[TypeofProblem]));
	CALCULATE(MAX('Problem'[TIME]))
)

With that I am able to take into account only the max value for each week-day-month for each type of problem, but if I do not select the Type of problem or if I select another filter it does not work well.

Does something know how to help me?

 

Thanks for your time reading this long post!

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @SSS

Based on my understanding, you want

1.A measure that gives the maximum value for each problem of each week.

2.the measure to take into account the different filters like type of problem, type of subject, etc.

As you said, 1 has been achieved by your measure, 2 is what you need currently.

 

“but if I do not select the Type of problem or if I select another filter it does not work well.”

Does this mean that the measure should reflect the changes for different types of problems without selecting in this filter but for another filter it is needed to select in other filters?

 

It is appreciated for to show us an example of your dataset and how visuals interact with these filters.

 

 

 

 

Best Regards

Maggie

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors