I have a bunch of engineers who get assigned work for each day and I have come up with a scoring system for how well each is utilized on a particular day. What i would like the is overall average score for each day for all engineers, but getting something different that is an average of something, but can't work out what. Each engineer may have several calls assigned for one day so the score is based on their total for the day, hence the need to SUMMARIZE first.
Code:
Eng Day Score = VAR
NewinHours=CALCULATE([Labour Hours],'Install Report'[CALL_TYPE]="NEWIN")
RETURN
SUMX(
SUMMARIZE(
'Install Report',
DateTable[Day],
'Install Report'[ENGINEER]
),
IF(
[Newin Jobs]>2,
5,
IF(
[Newin Jobs]=2 ||
AND([Newin Jobs]=1,NewinHours>=7),
4,
IF(
[Newin Jobs]=1 && NewinHours<7 && [Labour Hours]>=6,
3,
IF(
[Labour Hours]>4,
2,
1
)))))
Code:
[COLOR=#000000][FONT=SegoeUI]This is the result. So score for each engineer is right based on my if statement, but total in bottom table should clearly be 12. I tried wrapping the measure in another SUMX summarised just by day but that makes no difference. What is odd is some combinations of engineers on some days seem fine e.g. top table, but add in Adam and goes wrong. Change the top table to 23rd and calculation goes wrong gain. Perplexed[/FONT][/COLOR]
[COLOR=#000000][FONT=SegoeUI][IMG]https://oxcrx34285.i.lithium.com/t5/image/serverpage/image-id/168161iF0BC5981AA99ED1A/image-size/large?v=1.0&px=999[/IMG][/FONT][/COLOR]
[COLOR=#000000][FONT=SegoeUI]I have saved down a small version of the data here
[/FONT][/COLOR]
[COLOR=#000000][FONT=SegoeUI][URL="https://www.dropbox.com/s/1t2iaba2y46zjb5/Prism%20test.pbix?dl=0"]https://www.dropbox.com/s/1t2iaba2y46zjb5/Prism%20test.pbix?dl=0 [/URL][/FONT][/COLOR]
[COLOR=#000000][FONT=SegoeUI]
Any pointers appreciated, as my other X calculations seem to work as expected?
Mike
[/FONT][/COLOR]
Last edited: