How to compare measure value against aggregated / rolled up measure

[rax99]

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.

L1= Client

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?

[v-lili6-msft]

hi, @rax99 

Sample data would help tremendously.

Could you share a simple pbix file and the expected output for us?

Best Regards,

Lin

[rax99]

hi @v-lili6-msft 

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.

Thanks