@Curtis_Smith Okay, yeah that makes sense! Playing around a bit and here’s what I’ve come up with (for now at least!).
You can basically make a table that maps each Role-Difficulty combo to the correct effort level. Then, in your “Individual effort” column, you’d look up the correct row from that table (basically, find the row that matches this row and difficulty) and then grab the corresponding effort. It would look something like this!
First, your “Effort by Role” table. the Role column is a lookup to the “Hiring Team Roles” table, just like the ROLE column under “Roadmap & Resources”, so you can match them up.
Then back to “Roadmap & Resources”, you can write a filter in your “INDIVIDUAL EFFORT” column (this is where you want to get your weighted value, right?) that looks like:
[Effort by Role].Filter(Role=thisRow.ROLE AND Difficulty=thisRow.Difficulty).Effort.First()
Let me break down this formula really quick.
[Effort by Role].Filter(Role=thisRow.ROLE AND Difficulty=thisRow.Difficulty)
- Go to the “Effort by Role” table, and give me ONLY the rows where the Role is the same as the ROLE in this row, and the Difficulty the same as the Difficulty in this row. It will give you a list of all rows that match, in this case should be a list with one row in it.
- From this filtered down list of rows, grab the Effort value of each row (So it will be a list of length 1 that looks something like
- Grab the first item in the list. Because you have a list at this point with just one effort number in it, we want to grab the number itself instead of the list. So you can do
.First() to get the actual effort value inside.
Try that out and let me know if it works out for you!! From there every row should have the correct INDIVIDUAL EFFORT score automatically once the ROLE and Difficulty are chosen.