Forum Discussion
Solved
Weighted Average for Multiple Categories
Hey everyone, I've been trying to tackle this all morning and cannot seem to find a solution. What I'm looking to do is get a weighted average across multiple categories as the title says. ...
I figured it out!
I was unaware you could reference the same row being evaluated using the EARLIER operator, so I ended up with a function to get the weight that looks like this:Weight = DIVIDE( 'Job Analysis'[Remaining Hours], SUMX(FILTER('Job Analysis', 'Job Analysis'[Planner] = EARLIER('Job Analysis'[Planner]) && 'Job Analysis'[Start Monday] = EARLIER('Job Analysis'[Start Monday])), 'Job Analysis'[Remaining Hours]))
Then from there I simply multiply each row's slack by its weight.
Thanks to those of you who took the time to read my post, hopefully this solution might help others.
I figured it out!
I was unaware you could reference the same row being evaluated using the EARLIER operator, so I ended up with a function to get the weight that looks like this:
Weight = DIVIDE( 'Job Analysis'[Remaining Hours], SUMX(FILTER('Job Analysis', 'Job Analysis'[Planner] = EARLIER('Job Analysis'[Planner]) && 'Job Analysis'[Start Monday] = EARLIER('Job Analysis'[Start Monday])), 'Job Analysis'[Remaining Hours]))
Then from there I simply multiply each row's slack by its weight.
Thanks to those of you who took the time to read my post, hopefully this solution might help others.
Then from there I simply multiply each row's slack by its weight.
Thanks to those of you who took the time to read my post, hopefully this solution might help others.
- vanessafvg
Community Champion
ah well done sorry i was only going to look at it tonight. I think the only think with earlier is potentially to watch peformance. There is another way to do it with variables. but if you have solved the problem all good!
Good to know that EARLIER might be a drag on performance. I would certianly be open to doing it another way if it were easier on the system.
