Cannot get total row working

[Anonymous]

I have researched similar issues, but I still cannot solve this (pretty new to DAX BTW). 

Exec Summary: I want to know, by Project, how many employees are overallocated (>100%) across ALL their projects combined. 

Setup: I have 3 tables: Employee, Project, and % Allocated by Project that are all linked properly and producing correct output everywhere else.

So in this example:

Employee 1 is overallocated because total across all projects is >100% even though no individual project is >100%

Employee 2 is overallocated because total across all projects is >100% 

Employee 3 is not overallocated because total across all projects is not >100% 

The last column ('Over Allocated Across All Projects') does not exist, that is for reference only. Happy to make this a measure or column if needed, but rather not unless required. 

The section at the bottom in green is what I am after: By Project, sum up the total allocation for all employees and tell me the number of employees on that project that are overallocated across all their projects. Assume most projects will have some employees overallocated, some not. 

I went down a spiral or progressively more complex formulas before admitting defeat to what seems like should not be that hard?? 

Thanks for any help. 

[Daniel29195 · Accepted solution]

@Anonymous 

output 

measures 

Measure 5 = 
var ctr_emp =  DISTINCTCOUNT(Table2[employee])

var emp_val= VALUES(Table2[employee])

return ctr_emp & " (Emp " & CONCATENATEX(emp_val , Table2[employee] , " & " ) & " )"

Measure 6 = 
var ds = 
FILTER(
ADDCOLUMNS(
    VALUES(Table2[employee]
    ),
    "@allocated" ,  CALCULATE(sum(Table2[allocated]) , REMOVEFILTERS(Table2[project]))
),
[@allocated]>100
)

var ctr_emp =  COUNTROWS(ds)





return COALESCE(ctr_emp,0) & " ( Emp " & CONCATENATEX(ds , Table2[employee] , " & " ) & " )"

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🙏

[Anonymous]

OK, first of all - I wasn't even close 😞

And second, this worked 100% perfectly on first try. I'm so jealous of you guys that can actually figure this stuff out, but I'm working my way there.

Many thanks to you and I hope it helps someone else!