Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
This is the formula on my Excel, I basically want to have a similar formula in the PowerBI data table
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
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
No need for calculation
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.
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.