Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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! 🙏
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! 🙏
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!