Forum Discussion

bnevers's avatar
bnevers
Regular Visitor
6 years ago
Solved

Aggregating Across Common Values Part II

As a follow up to my previous post on  and building on the file provided by Ashish_Mathur I have this data table:

 

Then this measure: Hours spent = SUM(Data[Hours])

 

Which produces this table:

And then this

Measure = if(VALUES(Staff[Staff])=VALUES(Staff_dup[Staff]),BLANK(),CALCULATE ([Hours spent],CALCULATETABLE(SUMMARIZE(Data,Data[Project]),ALL(Staff),USERELATIONSHIP(Data[Staff],Staff_dup[Staff]))))

 

Which produces this Table:

 

Now I want to do two things:

1) Establish a minimum threshold of hours worked on a common project for it to count. In the example above, if the min threshold is 3 total horus worked by either individual, then the total hours A-B spent working on Project 1000 would be 0 (A only spent 2 hrs on Project 1000). Therefore the overall total would include only Project 2000 so the overall total for A-B would be 8 and B-A would be 3. 

2) Return a list of Common Projects worked on together between a selected pair of individuals (in the example above it's only Project 2000 with a min threshold of 3 hours ). 

 

Any advice is much appreciated!

 

Heres the file