Forum Discussion

kangkopi15's avatar
kangkopi15
Helper I
2 years ago
Solved

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]
    )
)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

7 Replies

    • kangkopi15's avatar
      kangkopi15
      Helper I
      2 years ago

      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.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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's avatar
    kangkopi15
    Helper I
    2 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        kangkopi15
        Helper I
        2 years ago

        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