Staff Utilisation

[Anonymous]

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

[az38 · Accepted 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

[Anonymous]

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

[az38]

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

[Anonymous]

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 !