How to calculate Utilization?

Question

Summary

Content

Hi,

I'm trying to create a kind of utilization report. Currently I have a data set which includes hours reported each day, e.g.

Resource | Hrs | Week Ending

john.doe | 2 | 27/05/2017

john.doe | 4 | 27/05/2017

john.doe | 2 | 27/05/2017

jane.doe | 8 | 27/05/2017

jane.doe | 10 | 3/06/2017

jane.doe | 8 | 10/06/2017

What I need to do is to calculate the totals per "Week Ending" and then divide this by 40 (which is the target utilization for a week).

My idea was to aggregate Hrs by Week Ending and then add calculation to divide by 40.

However, I have 2 issues with this:

1) If there is nothing reported for a give week yet (e.g. in future) I will not get any rows back - and I need at least the rows with 0 reported time for coming weeks

2) how to aggregate data by week end? (I'm trying the "Aggregate" step in Data Flow but cannot understand Group by/Aggregate columns to make it work.

Regards,

Pawel.

Pawel X Dmochowski · Answer

Thanks, this works!

Gabby Rubin-Oracle · Answer

Hi Pawel,

On #1 please see the response to the other post.

On #2, in the project define My Calculation which is Hrs/40. It should give you the value that you need. My Calculation formulas will always be performed after the aggregation that is applied to the measure in the Viz. The screenshot shows utilization per employee and week, where you can see that Mark and Jim had multiple entries in Week 2.

Regards,

Gabby