My apologies to for the slow progress on this. I have a huge project that is scheduled to go to production at the end of March, and am spending most of my waking hours trying to ensure we meet that deadline. Thus, I haven’t had a fraction of my usual time dedicated to the forum lately. However, this is a fascinating problem and I’m committed to getting you a solution as soon as I have time to get back to it, if you, @mickeydjw and others haven’t solved it by then. Again, sorry for the delay.
No worries - these are coronavirus times. With more time spent at home, I took a different angle to the challenge and came up with something that seems promising. I am working on it right now and will share once it proves positive. Basically, I’m going mathematical and finding another of calculating the team values without depending on direct reporting. Not clear now I know, but with more time at home (due to Covid-19) I have more time.
Big breakthrough, somewhat!
Not in Power BI per say but rather in the mathematics logic behind what the objective is. Previous I have based the calculation of the team score dependent on the total score of the direct reports, whose value in turn depend on their reports and so on. I discussed this with a math expert & we came up an alternative that seems easier to implement in Power BI. Rather than a hierarchical layered approach, we instead used a flat list of all employees within a team head dept to calculate his / her team score. Amazingly, the results turned out the same as the complicated hierarchical approach!
What do I mean? Using the image below for team head ‘Emp H’ as reference:
The virtual table displays the full list of employees with Emp H at the top - no reliance on levels - per month
I used LOOKUP function to obtain the ‘LM team weight’ column
My challenges:
I need to now scale up each employee weight by dividing their weight by the total weight per manager per month. For example, in the image, Emp L scaled weight will be = 1 / (1 + 0.8) = 0.56
(1 + 0.8) is the total weight for employees reporting to Emp K for that month
Once I have this scaled weight, next is to multiply this with the team weight of the respective manager. Stayin with our example, Emp K active weight is thus 0.56 * 0.15 = 0.0084
For direct reports (i.e. if manager column value equals the manager being evaluated, no scaling is done. The actual weight is used. For example, Emp K reports directly to Emp H & hence active weight is 0.85
It is this active weight to used with each employee score to calculated the weight score and give the final team value
Any insights how I can walk through steps 1 through 5? I believe it is now way lot easier to implement but still need help.
I have attached the updated PBI file with the virtual table 3. I also attach the excel prototype to demonstrate this alternative math method. both files below.
Many thanks for your time & help- much appreciate
Bare
Hi @Bare, It’s great to know that you are making progress with your query. Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate. For further questions related to this post, please make a new thread. More details can be found here - https://forum.enterprisedna.co/t/asking-questions-on-the-enterprise-dna-support-forum/30
Definitely open a new thread starting with your info on Post #42. I’m excited about the new mathematical approach you’ve taken and would like to dive in and work with you and @mickeydjw on this new tack.
I should be able to take a break this weekend from this production deadline I’m crunching on this week, and really dive into this (it’s not like I’m going anywhere these days…).
