Summing of a % measure issue (staff utilization)

[mrjkwon]

Wanting to pick anyone's brains over a dashboard I am looking to create in Power BI.

I have a transactional data table containing timesheet data for employees.

Employee	Date	Type	Hours	Revenue
Employee A	1/08/2021	Chargeable Time	8	800
Employee B	1/08/2021	Chargeable Time	8	960
Employee C	1/08/2021	Non-Chargeable Time	8	0
Employee A	2/08/2021	Chargeable Time	8	800
Employee B	2/08/2021	Chargeable Time	8	960
Employee C	2/08/2021	Non-Chargeable Time	8	0
Employee A	3/08/2021	Chargeable Time	8	800
Employee B	3/08/2021	Non-Chargeable Time	8	0
Employee C	3/08/2021	Chargeable Time	8	600
Employee A	4/08/2021	Non-Chargeable Time	8	0
Employee B	4/08/2021	Non-Chargeable Time	8	0
Employee C	4/08/2021	Chargeable Time	8	600
Employee A	5/08/2021	Non-Chargeable Time	8	0
Employee B	5/08/2021	Non-Chargeable Time	8	0
Employee C	5/08/2021	Chargeable Time	8	600

From the data above I have been able to create the following measures to calculate the following (when a filter or slicer is applied)

Employee A	Chargeable Time	24
Employee B	Chargeable Time	16
Employee C	Chargeable Time	24
Employee A	Total Hours	40
Employee B	Total Hours	40
Employee C	Total Hours	40
Employee A	Utilisation	60.0%
Employee B	Utilisation	40.0%
Employee C	Utilisation	60.0%
Employee A	Revenues	2400
Employee B	Revenues	1920
Employee C	Revenues	1800

I also have a fact table that contain budgeted utilisation for each employee

Employee	Budgeted Util
Employee A	65.0%
Employee B	50.0%
Employee C	45.0%

One of the challenges I've encountered is that the actual utilisation (a measure) will sum when no filter/slicer is applied, giving a value that is 160%, as opposed to 53.3%. 

I need to create a measure that will 

- Take the actual hours, divide by the actual utilisation (both measures), then multiply by the budgeted utilisation to arrive at a budgeted hours

- Take the actual revenue, divide by the actual utilisation (both measures), then multiply by the budgeted utilisation to arrive at a budgeted revenue

The summing of the % measure makes my desire for the above two measures somewhat challenging.

Any guidance, logic or recommendations of DAX formulas that I should be using will be greatly appreciated.

Many thanks.

[V-pazhen-msft]

@mrjkwon 

Here are the steps you can follow:

1. Create measure.

When no filter/slicer is applied, giving a value that is 160%, as opposed to 53.3%. 

HASONEVALUE =

IF(

    HASONEVALUE('Table2'[Employee]),MAX('Table2'[Budgeted Util]), SUMX(ALL('Table2'),'Table2'[Budgeted Util]))

Budgeted hours and budgeted hours:

budgeted hours =

DIVIDE( [groupABC_sum],[divide]) * MAX('Table2'[Budgeted Util])

budgeted hours =

DIVIDE( [groupABC_sum],[divide]) * MAX('Table2'[Budgeted Util])

2. Result:

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mrjkwon]

Many thanks for your help here! 

I wonder if I could seek your help with the review of the attached working file:

https://www.dropbox.com/s/35bfklhfdfigb88/Utilisation%20Report%20Excerpt.pbix?dl=0

Essentially I am trying to arrive at a measure for a tile that would help me fill the two tiles that are currently empty:

- the top tile for budgeted revenues, which conceptually would take the total number of direct hours, divided by the actual utilization and then multiplied by the budgeted utilization

- the bottom tile for budgeted revenues, which conceptually would take the total billable revenues, divided by the actual utilization and then multiplied by the budgeted utilization

There are select tiles that remain blank until a filter is applied on the dashboard. Whilst they have been configured to intentially remain blank, one of the tiles that I would actually like to show irrespective of whether a filter is applied, is the Utilization - Actual tile. Unfortunately I have been getting stuck with showing the utilization for the selected time period for the entire organization (it appears to sum up all the utilization values to a number that does not make sense)

Any light you could shed would be greatly appreciated!

[lbendlin]

@mrjkwon 

"I also have a fact table that contain budgeted utilisation"

That's not a fact table. That's a dimension table and it needs to be wired as such in your data model. Would you be able to show your current model (sanitized) ?

[mrjkwon]

Greetings, and many thanks for your reply.

Attached is an excerpt of the model.

https://www.dropbox.com/s/35bfklhfdfigb88/Utilisation%20Report%20Excerpt.pbix?dl=0

Essentially I am trying to arrive at a measure for a tile that would help me fill the two tiles that are currently empty:

- the top tile for budgeted revenues, which conceptually would take the total number of direct hours, divided by the actual utilization and then multiplied by the budgeted utilization

- the bottom tile for budgeted revenues, which conceptually would take the total billable revenues, divided by the actual utilization and then multiplied by the budgeted utilization

There are select tiles that remain blank until a filter is applied on the dashboard. Whilst they have been configured to intentially remain blank, one of the tiles that I would actually like to show irrespective of whether a filter is applied, is the Utilization - Actual tile. Unfortunately I have been getting stuck with showing the utilization for the selected time period for the entire organization (it appears to sum up all the utilization values to a number that does not make sense)

Any light you could shed would be greatly appreciated!