Forum Discussion
Helper IAverage for a group in a different table
I am looking to caclulate the average hours worked by day by group using the following data from two different tables:
TABLE 1
| Date | Name | Group ID | Hours Worked |
| 1/6/2023 | John Doe | 123 | 4.5 |
| 1/6/2023 | John Doe | 123 | 4 |
| 1/6/2023 | Jane Doe | 123 | 9 |
| 1/7/2023 | John Doe | 123 | 5 |
| 1/7/2023 | John Doe | 123 | 4 |
| 1/7/2023 | Jane Doe | 123 | 10 |
| 1/6/2023 | First Last | 456 | 11 |
| 1/7/2023 | First Last | 456 | 10.5 |
TABLE 2
| GROUP ID | GROUP NAME |
| 123 | GROUP 1 |
| 456 | GROUP 2 |
Thank you
Hi slatka11
You can create a measure with this code:
Avg of Hours Worked = DIVIDE(SUM('Table 1'[Hours Worked]),DISTINCTCOUNT('Table 1'[Name]))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
4 Replies
v-jingzhangCommunity Support
Hi slatka11
Do you want something like below?
You just need to create a relationship between two tables on "Group ID" from "Table 2" (one-side) to "Table 1" (many-side). Then add "Group Name", "Date" and "Hours Worked" columns to a table visual. By default "Hours Worked" will be aggregated by SUM. Click the down arrow next to the field name and switch to use Average.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
slatka11Hi v-jingzhang
This is what I am looking for, however, the averages are calculating incorrectly. I want the total average hours by group, by day, but because in the sample data provided John Doe has two separate data points for hours worked on 1/6/2023 (4.5 and 4 hours) the average is calculating lower than it should be (4.5+4+9/3) vs. what I am after (8.5+9/2). Could you please tell me how to correct this? Thank you.
- v-jingzhang
Hi slatka11
You can create a measure with this code:
Avg of Hours Worked = DIVIDE(SUM('Table 1'[Hours Worked]),DISTINCTCOUNT('Table 1'[Name]))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.