Compute Utilization Based on Data from 2 Tables

[polaris3028]

Hi PBI Gurus,

I'm pretty new with relationships in PBI and need help on my case.

Scenario:

I have TABLE#1 with details on #TASKS and #HOURS(per task) that can be summarized per month,
and TABLE#2 with fixed monthly values of WORKDAYS, HEADCOUNT AND WORKHOURS(8)

I will use it to compute for utilization using the formula:
UTILIZATION % = (#TASKS*#HOURS) / (WORKDAYS*HEADCOUNT*WORKHOURS)

Say:
#TASKS = 1387
#HOURS = 0.17
WORKDAYS = 19
HEADCOUNT = 3
WORKHOURS = 8

UTILIZATION %
= (1387*0.17) / (19*3*8)
= 235.79 / 456
= 51.71%

Note: TABLE#2 contains fixed values of the monthly workdays, headcount and workhours.

What's the best way to do this in PowerBI? Thanks in advance. 🙂

[v-xjiin-msft]

@polaris3028

To achieve your requirement, first you should create a relationship for the two tables. Go to Modeling - > Manage Relationships

Then you can simply create a new calculated column with expression like:

UT =
CALCULATE ( SUM ( 'Table#1'[Tasks] ) ) * CALCULATE ( SUM ( 'Table#1'[Hours] ) )
    / ( 'Table#2'[WorkDays] * 'Table#2'[HeadCount]
* 'Table#2'[WorkHours] )

Thanks,
Xi Jin.

[polaris3028]

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!

[polaris3028]

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!

[v-xjiin-msft]

@polaris3028

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.

[polaris3028]

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.  

[polaris3028]

If this helps, I'm sharing the report that I'm currently working on. 🙂

Report Link

Thank you.

[v-xjiin-msft]

@polaris3028

I have to say your report is pretty complicated. I have only checked the Danil's Utilization Page. And it seems like there exists several issues.

First since you are using a slicer (MonthID) to filter the source table. And your UT expression is like:

%Utilization_Danil = IF(OR(All_Consolidated[Cluster]="Screens",All_Consolidated[Cluster]="IQ"),
CALCULATE ( SUM ( All_Consolidated[#NewVol] ) ) * CALCULATE ( AVERAGE(All_Consolidated[#NewBM] )
    / ( AVERAGE('Danil''sPodUtilTable'[Workdays]) * AVERAGE('Danil''sPodUtilTable'[Headcount])
* AVERAGE('Danil''sPodUtilTable'[Workhours]) )Danil's Utilization),IF(OR(All_Consolidated[Cluster]="Mapping",All_Consolidated[Cluster]="Reporting"),
CALCULATE ( SUM(All_Consolidated[Total Time]) )
    / ( AVERAGE('Danil''sPodUtilTable'[Workdays]) * AVERAGE('Danil''sPodUtilTable'[Headcount])
* AVERAGE('Danil''sPodUtilTable'[Workhours]) )))

It is wrong to use CALCULATE ( AVERAGE(All_Consolidated[#NewBM] ) to get the Avg All_Consolidated[#NewBM]. You can simly check this detail table:

As you can see, I have expanded your matrix to a table. There exists 17 rows Reporting on 2018_01. The Sum #BM is 177.99. So its avg should be 10.47. However when using CALCULATE ( AVERAGE(All_Consolidated[#NewBM] ) directly to calculate, the result is 0.97 which is wrong.

Then for the issue you are using Average() function to get HeadCount or WorkingHours. You can modify them with expression like CALCULATE(MAX('Danil''sPodUtilTable'[Headcount])).

Also, it will be more helpful that if you can share us your desired result for one of your report. So that we can know the right direction. 

Thanks,
Xi Jin.

[v-xjiin-msft]

@polaris3028

In your scenario, if table 2 has a column called Cluster? Does table 1 have the same column Cluster? If so, you can use CONCATENATE() function to create a unique column in both two tables, and create relationship on this unique column for them.

Please refer:

By the way, in your sample data. Let's say the green row, if Hours is 59.13, with your shared formula, how do you get the UT as 51.71%? If you have a new calculate logic, please share it to us.

Thanks,
Xi Jin.