Utiisation per week

[kangkopi15]

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]

    )

)

[kangkopi15]

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

[v-yiruan-msft · Accepted solution]

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

[kangkopi15]

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

[lbendlin]

No need for calculation

[kangkopi15]

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.

[v-yiruan-msft]

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

[kangkopi15]

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