Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jfpalumbo
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. 

 

Capture.PNG

 

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@jfpalumbo 

output 

Daniel29195_0-1710448722683.png

 

 

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

View solution in original post

2 REPLIES 2
Daniel29195
Super User
Super User

@jfpalumbo 

output 

Daniel29195_0-1710448722683.png

 

 

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!

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.