Forum Discussion
Compute Utilization Based on Data from 2 Tables
Hello v-xjiin-msft,
Appreciate the reply. Apologies, but the requirements changed.
Here's the details:
Table 2 should have these breakdown where it shows that there are 4 clusters in a month with different headcount.
Now, I'd like to have the utilization column added in Table 1 instead so that if needed I can have a report created like this:
I have verified that the row with Green shade is computed correctly, just need to know how to relate to show the correct headcount workdays, etc for each cluster that is from the Table 2.
Thanks thanks!
here you go, v-xjiin-msft. UT result looks good, but when I add up those columns from Table 2 like headcount, it's not showing the values for the specific cluster, instead it's showing the average of the entire headcount in the table. And I use the Average as shown in the calculated column for UT. Also, I've changed the relationship to use the concatenated column with the Cluster field along with the year/month column. I just don't know why it's not getting it right from table 2 to to show in the report. Please advise.
Thanks!
For current month, the HeadCount or Workdays or Workhours are fixed for each Cluster. And you are calculating the UT for each cluster. Right? Then why are you using Average?
After creating the relationship, the UT will calculated based on each concatenated column group automatically. There's no need to use average. And if you want to calculate a total UT for all culsters. Then you should create a new measure or column.
Thanks,
Xi Jin.Appreciate your response v-xjiin-msft.
Let me share the table I have created for this report.
So, I created this table and linked it with the one with recordsets using the TableID. I'm not sure how to do it to just use Sum instead of Average when calculating the utilization? Can you help me with the formula? It looks like it's getting the sum of the entire table instead of just the specific tableid.
Thanks in advance.
