topic Re: Utiisation per week in Developer

Utiisation per week

kangkopi15 — Wed, 28 Feb 2024 13:36:25 GMT

Dear Experts,

I need your help again, I want to sum total the utilization per week, except for the activity called "Leave"

Activity	Utilisation %	Start Date	Start Date of the week	Resource	End Date of the week
Project	100	26/02/2024	26/02/2024	Upik	29/02/2024
Project	100	01/03/2024	01/03/2024	Ujang	03/03/2024
Sales	50	26/02/2024	01/03/2024	Upik	03/03/2024
Leave	20	01/03/2024	01/03/2024	Upik	03/03/2024
Leave	30	01/03/2024	04/03/2024	Tono	10/03/2024
Sales	45	04/03/2024	04/03/2024	Arif	10/03/2024
Project	78	26/02/2024	04/03/2024	Arif	10/03/2024
Project	60	01/03/2024	04/03/2024	Arif	10/03/2024
Leave	30	01/03/2024	11/03/2024	Joko	17/03/2024
Project	85	04/03/2024	11/03/2024	Supri	17/03/2024
Project	89	26/02/2024	11/03/2024	Jon	17/03/2024
Sales	75	01/03/2024	11/03/2024	Sapri	17/03/2024
Leave	20	01/03/2024	18/03/2024	Bowo	24/03/2024

I tried with this syntax, appears to be sumarize all the utilization

Weekly Utilisation = CALCULATE(

SUM('Data'[Utilisation %]),

FILTER('Data',

'Data'[Resource] = 'Data'[Resource] &&

'Data'[Start Date of the week].[Day] = 'Data'[Start Date of the week].[Day] &&

'Data'[End Date of the week].[Day] = 'Data'[End Date of the week].[Day]

)

Re: Utiisation per week

lbendlin — Thu, 29 Feb 2024 00:11:08 GMT

No need for calculation

Re: Utiisation per week

kangkopi15 — Thu, 29 Feb 2024 06:24:44 GMT

ah you're right, thank you, however, I have this formula in my Excel, and I wonder can apply the same for powerBI, essentially I want to have the average utilisation per week.

Re: Utiisation per week

Anonymous — Thu, 29 Feb 2024 09:45:14 GMT

Hi @kangkopi15 ,

You can follow the steps below to get it:

1. Create table visual

2. Apply the field [Resource], [Start Date of the week], [End Date of the week], [Utilisation %] with the aggregation Sum (which just like suggested by @lbendlin ) and [Utilisation %] with the aggregation Average as below screenshot

Best Regards

Re: Utiisation per week

kangkopi15 — Thu, 29 Feb 2024 12:22:22 GMT

hi @Anonymous @lbendlin thank you, appreciate your response, I followed your steps and managed to get the table, I think the reason why I need the formula is for the filter and another visualization, the actual table is more comprehensive with more columns and tables, that's why I need to have the correct measurement

Re: Utiisation per week

kangkopi15 — Fri, 01 Mar 2024 04:14:35 GMT

This is the formula on my Excel, I basically want to have a similar formula in the PowerBI data table

Re: Utiisation per week

Anonymous — Fri, 01 Mar 2024 05:47:59 GMT

Hi @kangkopi15 ，

You can create a calculated column as below to get it, please find the details in the attachment.

Utilisation per week = CALCULATE( SUM('Table'[Utilisation %]), FILTER( 'Table', 'Table'[Start Date of the week] = EARLIER('Table'[Start Date of the week]) && 'Table'[Resource] = EARLIER('Table'[Resource]) && 'Table'[End Date of the week] = EARLIER('Table'[End Date of the week]) ) ) / 100

Best Regards

Re: Utiisation per week

kangkopi15 — Fri, 01 Mar 2024 06:24:24 GMT

Hi @Anonymous lovely, thank you, that's correct.
I tried using this syntax, and it gives me the same result as yours

Weekly Utilisation = CALCULATE(SUM('Table'[Utilisation %]),
ALLEXCEPT('Table',
'Table'[Resource], 'Table'[Start Date of the week].[Date], 'Table'[End Date of the week].[Date]
)
) / 100

However your solution is the most acceptable, so I am going your syntax, thank you