I have created a virtual table I want to use as a variable to calculate an overall score for a department head:
The business logic is to arrive at the overall score for a manager as depicted in the prototype below, using LM 3 as the manager here:
Can one help or point me in the right direction (if a video on EDNA already exists) to iterate over the variable / virtual table to calculate for each month for each row, the overall score for selected manager by:
- Dividing the weight of each Employee ID by the total weights of employee reporting in the same manager to obtain ‘scaled weight’
- Then multiply the result of step 1 above with the value of the column ‘LM team weight’ to give the column ‘Actual weight’
- And finally, multiply result of ‘Actual weight’ with values in column ‘Score’, row by row
- The final step is to then divide the result of step 3 with the total of value of column ‘Score’
There is one ‘IF’ statement logic: if an employee’s manager is the same as the selected manager, no transformation is done. that is, Step 1 is ignored & the value of ‘LM team weight’ will be 100%.
I have attached both sample PBIX file & the Excel working prototype for clarity.
Many thanks in advance
overview of prototype.xlsx (17.7 KB)
Emp Dashboard v5.pbix (166.1 KB)