Forum Discussion
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.
The above is the output I'm looking for.
Weight = Load / SUM(Load per work week per category)
Weighted Slack = Slack * Weight
Forgive me if I'm not explaining that well, but effectively that translates to E2 = C2 / SUM(C2,C3) and F2 = D2*E2
Then I will use a matrix to summarize the total weighed slack for a given week and given category which would look someting like this:
First I started by thinking I could use the CALCULATE function in DAX, but it seems I would have to make a filter for each work week for each category. Then I was thinking I could simply do a calculation for the weight in a new column Load / SUM(Load) and let the matrix do the grouping but it seems that the calculation is done with all categories and weeks then summarized in the matrix instead of filtered.
I've tried so many things at this point, probably everything except the correct solution if one exists. Does anyone know how I might be able to accomplish this?
Note: I don't really know how to accomplish this in excel either, so I have put the formulas together manually to get the correct output.
Weighted Slack Example PBI
Weighted Slack Example Excel
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.
5 Replies
if you can provide some sample data in a power bi file that would be useful to getting your problem solved 🙂 or at least in excel.
Good suggestion, I have added links to two example files that use real world data trimmed down.
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.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.
