Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rax99
Helper V
Helper V

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
v-lili6-msft
Community Support
Community Support

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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors