Forum Discussion
Helper IAverage for a group in a different table
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.
Community SupportHi 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.
Helper IHi 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. - Ashish_Mathur
Super User
