I’m stuck with a challenge I am hoping someone can help / assist me with. Or maybe there is something similar in Enterprise DNA library you can point me to?
Case scenario
The organizational structure I am working with is shown:
Metric scores are measured against each employees based on assigned weights to metrics. Department heads have additional weights for team score performance: they are measured for score based on the score performance of their direct reports (in addition to their own metric score). For example, Emp H as department head has a weight of 0.2 for team score performance (see attached PBI sample file). This will then be multiplied against each of the total scores of direct reports I, J, K for a weighted average result. The same is repeated for Emp K. but as Emp I & J are not department heads, they only have their score performance.
I have calculating the scores for all employees and created calculated columns for the path hierarchies (somehow, I believe this will be key?)
Challenge
Calculating department head score using weights as given in the table ‘DeptheadWeights’.
@Bare
Ah, the old calculating hierarchy data. This can be done in DAX, but will take some time. The main reason is that DAX doesnt have hierarchy functions built-in besides the PATH, PATHCONTAINS, etc, which are more or less information functions rather than actual hierarchy functions. MDX (what pivot tables are actually written in) have buit-in hierarchy functions which is why you can see things like “% of parent column” in a pivot table.
I’ll spend some time on this to at least get you started in the right direction.
A colleague & I looked at this critically and came up a ‘creative’ angle to solving
First we wanted a way to see a list of employees if a manager is selected (I have attached an updated pbi file). So initially we created a test table using CALCULATETABLE:
Test tabe =CALCULATETABLE(
VALUES(
Employee[Employee ID]),
FILTER(
ALL(Employee),
Employee[Manager ID] = “Emp B”
)
)
We then applied this to a measure to obtain the ‘individual score’ for each employee reporting to a manager if the manager is selected:
Team Score =
VAR SelectEmp = SELECTEDVALUE(Employee[Employee ID])
RETURN
CALCULATE(
[Individual Score],
Employee[Employee ID] IN
CALCULATETABLE(
VALUES(Employee[Employee ID]),
FILTER(
ALL(Employee),
Employee[Manager ID] = SelectEmp
)
)
)
The ‘SELECTEDVALUE’ working with the slicer as shown in the attached file.
And this is where we are stuck - for 2 challenges you or anyone may help us with. (a) we couldn’t figure out a way to display the employees reporting to Emp B, for example, in a table visual to display their ‘Individual Score’ (seemed to work in the ‘Test table’ where the employee is hard coded for test purposes. Not sure why it won’t with the selected value). (b) we are looking for an outcome of the ‘Team Score’ for the manager to be an average of all ‘Individual Score’ for each reporting employee where these scores exist i.e. employees reporting but with no scores are ignored for the average. A sort of averaging a group by for each employee (right now what we get a totaling the whole column to ‘recalculate’ the ‘individual score’!). An example, for the month of Dec 2018, we expect a ‘team score’ for Emp B to be 103.765 (an average of the individual scores of employees C (100) and D (107.53) reporting to Emp B). But our number shows 103.56 instead!
Can you help? I hope I have been as detailed as helpful. Otherwise do please let me know.
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
