cancel
Showing results 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

Regular Visitor

## Cannot get total row working

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.

1 ACCEPTED SOLUTION
Super User

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(
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! 🙏

2 REPLIES 2
Super User

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(
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! 🙏

Regular Visitor

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!