Scratching my head over SUMX

masplin

Active Member
Joined
May 10, 2010
Messages
413
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:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Found the answer to my own question. Seems that the Variable is outside of the SUMMARIZE function so this is being calculated for all engineers in the total making Labour Hours artificially high. solution was to build a measure to calculate the score then put that measure as the SUMX expression
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top