Forum Discussion
Staff Utilisation
Hi,
I'm trying to do something which I thought would be really simple but obviously not for me so here I am. I have a table similar to the one below. I am trying to work out staff %age utilisation for each employee for each period. Transaction Type 100 is utilised, all other Transaction Codes are not utilised.
Any help greatly appreciated.
| Employee No. | Transation Type Code | Units | Period |
| 1000 | 100 | 156.02 | 1 |
| 1000 | 103 | 1.86 | 1 |
| 1000 | 300 | 50.87 | 1 |
| 1000 | 100 | 1.25 | 2 |
| 2000 | 425 | 30.00 | 1 |
| 2000 | 100 | 240.00 | 1 |
| 2000 | 100 | 177.50 | 2 |
| 2000 | 300 | 6.50 | 2 |
| 2000 | 400 | 8.00 | 2 |
| 3000 | 100 | 103.50 | 1 |
| 3000 | 300 | 5.00 | 1 |
| 3000 | 400 | 8.75 | 1 |
Hi Anonymous
try new table
Staff Utilisation = SUMMARIZE('Table1';Table1[Employee No.];Table1[Period];"%utilization";divide(calculate(SUM(Table1[Units]);'Table1'[Transation Type Code]=100);calculate(SUM(Table1[Units]));0))do not hesitate to give a kudo to useful posts and mark solutions as solution
4 Replies
Hi Anonymous
try new table
Staff Utilisation = SUMMARIZE('Table1';Table1[Employee No.];Table1[Period];"%utilization";divide(calculate(SUM(Table1[Units]);'Table1'[Transation Type Code]=100);calculate(SUM(Table1[Units]));0))do not hesitate to give a kudo to useful posts and mark solutions as solution
Hi Anonymous
in power query mode set all digit columns as Number types
go to the Query Editor, set mouse on column and in Transform ribbon choose Data Type Whole Number or Decimal Number (for Units column)
maybe.. you will need to replace "." to "," in Units column (it depends on your localization)
do not hesitate to give a kudo to useful posts and mark solutions as solution