Forum Discussion
CountRows and Divide
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!!!
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
5 Replies
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
Hi Anonymous
If we answered your question, please mark the post as solution, this will also help others.
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
Maybe this helps...
