Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 proj...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

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

Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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!