Forum Discussion

silagy's avatar
silagy
New Member
8 years ago
Solved

Calculating Resource Utilization in Power BI

I am trying to calculate a resource utilization per day. My tables are: ResourceAllocations AllocationID - Int - Unique identifier of a resource allocation ResourceID - Int - Unique identifier o...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi silagy,

     

    WorkingTimePerDays is calculated column, you can get 1440 by changing the summarization to "Don't summarize". The file is here: https://1drv.ms/u/s!ArTqPk2pu-BkgT5MvD6Wbmcw5vbG.

    You can try a measure like this:

     

    Measure =
DIVIDE (
    [TotalAllocations] + [TotalUnAvailabilityTime],
    MIN ( 'rawViews Bks_DWH_ResourcesAllocations'[WorkingTimePerDay] ),
    0
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi silagy,

 

If you want to create a measure, you can use SUM and SUMX. Then add 'Calendar'[Date] and [ResourceID] to a table visual, which will provide the context of measure. 

'Calendar'[Date]   [ResourceID]   [measure]

 

If you want more help, please provide a sample pbix file.

 

Best Regards!

Dale

silagy's avatar
silagy
New Member
8 years ago

v-jiascu-msft

Thanks!

 

Here is a link to the pbix

 

I placed a table on the canvas to help explain what I need.

 

As you will see on the example, the WorkingTimePerDays showing 2880 which is 1440 * 2 (Because there are 2 rows on that day).

I need to get the distinct value of working time which is 1440 minutes on a single day

 

I would like to calculate the utilization of each resource per day.

 

 

Thanks!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi silagy,

     

    WorkingTimePerDays is calculated column, you can get 1440 by changing the summarization to "Don't summarize". The file is here: https://1drv.ms/u/s!ArTqPk2pu-BkgT5MvD6Wbmcw5vbG.

    You can try a measure like this:

     

    Measure =
DIVIDE (
    [TotalAllocations] + [TotalUnAvailabilityTime],
    MIN ( 'rawViews Bks_DWH_ResourcesAllocations'[WorkingTimePerDay] ),
    0
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale