Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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 Az38,
I'm getting an error DAX comparison operations do not support comparing values of type Text with values of type Interger. Consider using the VALUE or FORMAT function to convert one of the values.
I've looked at my data and only the units are Number the rest are text ?
Thanks
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
Hi az38,
Legend !! I found it, it was treating the Transaction Type code as a number. I put "" around the 100 code and worked perfectly.
Thanks for your help !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |