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, an...
Need Help
polaris3028's avatar
polaris3028
Helper III
8 years ago

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

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

 

Report Link

 

Thank you.

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

    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.