Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kangkopi15
Helper I
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"

ActivityUtilisation %Start DateStart Date of the weekResourceEnd Date of the week
Project10026/02/202426/02/2024Upik29/02/2024
Project10001/03/202401/03/2024Ujang03/03/2024
Sales5026/02/202401/03/2024Upik03/03/2024
Leave2001/03/202401/03/2024Upik03/03/2024
Leave3001/03/202404/03/2024Tono10/03/2024
Sales4504/03/202404/03/2024Arif10/03/2024
Project7826/02/202404/03/2024Arif10/03/2024
Project6001/03/202404/03/2024Arif10/03/2024
Leave3001/03/202411/03/2024Joko17/03/2024
Project8504/03/202411/03/2024Supri17/03/2024
Project8926/02/202411/03/2024Jon17/03/2024
Sales7501/03/202411/03/2024Sapri17/03/2024
Leave2001/03/202418/03/2024Bowo24/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

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

vyiruanmsft_0-1709272024219.png

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.

View solution in original post

7 REPLIES 7
kangkopi15
Helper I
Helper I

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

kangkopi15_0-1709266403173.png

 

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

vyiruanmsft_0-1709272024219.png

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.

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
Super User
Super User

No need for calculation

 

lbendlin_0-1709165458928.png

 

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.

 

kangkopi15_0-1709173626392.png

 

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

vyiruanmsft_0-1709199602430.png

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.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.