cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Can I create a new table based on a measure?

I have a data set with person number, week number, and hours worked for that day. I'm trying to calculate the average number of hours per week each employee worked, and then be able to group the COUNT of personIDs with the same number of average number of hours worked. Essentially answering the question, how many employees worked an average of 8 hours per week.

I was able to calculate the average number of hours per week per employee, but when I tried to map it to a table with count of person ID it did not work at all.

My calculation for average hours per weeks looks like this...

AvgHrPerWeek = ROUND(AVERAGEX(
KEEPFILTERS(VALUES('Table'[WeekNumber])),
CALCULATE(SUM('Table'[HoursWorked]))),0)

When placed in a table with PersonID this works great as it calculates each average hours per PersonID. The next step is the tricky one where I want to essentially take that PersonID and calculated measure of Average Hours and then calculate how many PersonIDs have the same AverageHours and map that on a graph with AverageHours on the horizontal axis and PersonID on the vertical.

 PersonID WeekNumber HoursWorked 0023 1 8 0023 1 6 0023 4 9 0023 4 4 0023 6 2 0023 6 6 0023 6 6 0023 6 3 0025 1 8 0025 1 6 0025 4 4 0027 4 2 0027 4 5 0029 2 7 0029 2 8 0029 6 2

Responsive Resident

## Re: Can I create a new table based on a measure?

Hi, unfortunately a measure can't be used in the same way as a column. In this case I suggest that you create a new calculated table with the average hours by PersonID. You can do that by wrapping your own measure in a SUMMARIZE like this:

PerPerson = SUMMARIZE('Table', 'Table'[PersonID]; "AverageHours"; ROUND(AVERAGEX(
KEEPFILTERS(VALUES('Table'[WeekNumber])),
CALCULATE(SUM('Table'[HoursWorked]))),0))

From that table it should be easy to create the visual you want.

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors