cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99 Member
Member

How to compare measure value against aggregated / rolled up measure

So I have a matrix that measures the variance of teams/departments/agents performences by using standard deviations;

 

matrix.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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?

2 REPLIES 2
Community Support Team
Community Support Team

Re: How to compare measure value against aggregated / rolled up measure

hi, @rax99 

Sample data would help tremendously.

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

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rax99 Member
Member

Re: How to compare measure value against aggregated / rolled up measure

hi @v-lili6-msft 

 

see attached pbi file link

 

 

matrix2.JPGIn 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