cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Utiisation per week

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]
)
)
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Helper I

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

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Hi @v-yiruan-msft 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

Super User

No need for calculation

Helper I

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.

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

hi @v-yiruan-msft @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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors