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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.