Forum Discussion

polaris3028's avatar
polaris3028
Helper III
8 years ago

Compute Utilization Based on Data from 2 Tables

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. :)

 

Need Help

8 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

     

    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's avatar
      polaris3028
      Helper III
      8 years ago

      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!

       

       

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        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.