Calculate team/employee utilization for drilldown?

[sylwir061]

Hi!

I need to prepare an employee utilization report (preferably drill-downable), which should show following data:

1. Utilization per business area

2. Utilization per team (multiple teams in each business area but not cross-areas)

3. Utilization per single person (and unfortunately, some people are present in more than one team, some of them even participate in teams from different business areas...)

Utilization is calculated classically as: billable time divided by entire time logged in a timesheet by a an employee (followed by aggregated time calculations for teams and business areas).

My question 1 is: can it be done autimatically or do I have to prepare a separate table with calculations for each of the 3 levels?

And if I need to make it through the tables - question 2: will it be possible to prepare drill-down report that way?

I am pretty new to PowerBi, I tried to search for information on some neat way to make without additional tables but did not find any that I could use. I will appreciate an answer - even if it is: make those **bleep** tables & sorry, no drilldown 😄

[Sahir_Maharaj]

Hello @sylwir061,

Question 1:

- In Power BI, you can create calculated measures and hierarchies to calculate utilization at different levels without the need for separate tables.

- You can leverage the existing data in your dataset and use DAX (Data Analysis Expressions) calculations to perform the necessary calculations. Calculated measures can aggregate data dynamically based on your report's context, allowing you to drill down into different levels of detail.

Question 2:
- Yes, it is possible to create a drill-down report using tables in Power BI.

- You can create a table visual that displays utilization at the business area level, and then drill down into teams within each business area, and further into individual employees within each team.

- Power BI allows you to define drill-through actions on specific columns or measures, enabling users to navigate to more detailed information by clicking on the respective data points. To achieve this, you'll need to create hierarchies in your data model and set up drill-through actions in your report. The hierarchies will define the relationships and levels of detail in your report, while the drill-through actions will specify the target report pages or visuals to navigate to when drilling down.

- I would recommend starting by organizing your data in a well-structured format and setting up relationships between the relevant tables in your data model. From there, you can create calculated measures and hierarchies based on your utilization calculations and set up drill-through actions to enable the desired drill-down functionality.

While it may require some initial setup and understanding of Power BI's features, you should be able to achieve the employee utilization report you described with drill-down capability. Feel free to ask any specific questions as you start working on your report!