So I have a matrix that measures the variance of teams/departments/agents performences by using standard deviations;
As you can see (from my rather crude drawing, for which I apologise) it has several layers in the agent hierarchy.
L2 = Dept
L3 = Team
L4 = Agent
The goal is to find how many seconds a dept/team/agent is outside of the standard deviation for that specific group.
So in the above example:
The STDEV for Client1 is 406 seconds across all records, and 98872602 seconds across all rows sit above 406 seconds. Calculated as below:
AboveSD = VAR SD1 = CALCULATE(STDEV.P('PBI Fact_ScorecardMasterTable'[Duration])) RETURN SUMX(FILTER('PBI Fact_ScorecardMasterTable', 'PBI Fact_ScorecardMasterTable'[Duration]>=SD1),'PBI Fact_ScorecardMasterTable'[Duration])
so the above is just iterating through each row to find where [Duration] >= 406 (which is VAR SD1) and then summing up the values.
The formula is working how it should however what I would like it to do is to compare the durations of the current level to the SD of the level above.
To clarify what I need it to do;
The individual Teams (L3) durations should compare to the SD value of the level above, so in this case Department (L2)
So from the above example,
Team 1 should show me a sum of all durations that are above 289, however its showing me a sum value of all durations above 292 which is not correct.
and same with Team 2 should show me a sum of all durations that are above 289, however its showing me a sum value of all durations above 208 which is not correct.
And again at the dept level,
CUSTOMER SERVICE should show me a sum of all durations above 406, however it is showing me all durations above 289 which is not correct.
Any idea how I can tell the measure to compare against the level above?
Sample data would help tremendously.
Could you share a simple pbix file and the expected output for us?
see attached pbi file link
In the link you will have something like this.
Rows are showing the hierarchy levels. (ClientAAA>Dept_1>Team_13>Employees)
Hopefully the AboveSD measure makes sense.
The AboveSD measure should compare against the STDEVP.1_Duration of the level above the current level, rather than its own level. So currently the 1592 for Team_13 is the sum of values above 133. However, this should be the sum of values above 194.
Hopefully this makes sense.