CountRows and Divide

[Anonymous]

Morning, 

We have been beating our heads againist the desk for a few days now on this problem.  Here is our table structure 

Table 1 - Employee

Table 2 - TimeEntry 

EmployeeID

FirstName

LastName

Department

EmployeeID

BilledHours

ProjectID

ProjectName

Basically what we are wanting to do is count the number of employees for a department from Table 1, lets say 'IT'  And then divide BilledHours in Table2 by the number of employees whose department is = IT  

We have tried using a measure using CountRows and it doesn't move between tables it appears.  

Any help would be appreciated!!! 

[v-juanli-msft · Accepted solution]

Hi @Anonymous

mwegener's solution may be helpful if you want to create a table visual like that.

Why you use a measure using CountRows doesn't work is that as a measure it calculate the current row instead of the filter content. you need to use "calculate" function to change the filter content for "countrows".

If you'd like the "count" or "countrows" to use in your Table 1 - Employee to calculate the count the number of employees for each department

Please create measures like

each department = CALCULATE(COUNT(Employee[EmployeeID]),ALLEXCEPT(Employee,Employee[Department]))

If you want divide BilledHours in Table2 for each EmployeeID by the number of employees per department, please create measures below

sum per id = SUM(TimeEntry[BilledHours])

divide1 = SUM(TimeEntry[BilledHours])/[each department]

If you want divide BilledHours in Table2 for each department by the number of employees per department, please create measures below

sum per depart = CALCULATE(SUM(TimeEntry[BilledHours]),ALLEXCEPT(Employee,Employee[Department]))

divide2 = [sum per depart]/[each department]

Best Regards

Maggie

[mwegener]

Hi @Anonymous

If we answered your question, please mark the post as solution, this will also help others.

[mwegener]

Maybe this helps...

[richbenmintz]

Hi @Anonymous

assuming you have a 1->* relationship between Table 1 and Table 2, the following should work

measure = divide(sum('table2'[BilledHours]), countrows('table1') )

Hope that helps

[Anonymous]

@Anonymous Publish an example with some sample data of what you want and you'll get an answer in no time.

---