Forum Discussion

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful Individual
7 years ago
Solved

Scratching my head over AVERAGEX

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 utilissed on a particular day. What i woudl like the is overall average sc...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi masplin 

    You may create a condition measure.Then get the Eng Day Score with it.

    Condition =
VAR NewinHours =
    CALCULATE ( [Labour Hours], 'Install Report'[CALL_TYPE] = "NEWIN" )
RETURN
    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, IF ( [Labour Hours] > 0, 1, 0 ) )
            )
        )
    )

    Eng Day Score = 
VAR
NewinHours=CALCULATE([Labour Hours],'Install Report'[CALL_TYPE]="NEWIN")
                              
                            
RETURN
      
          AVERAGEX(
                     SUMMARIZE(
                              'Install Report',
                              DateTable[Day],
                              'Install Report'[ENGINEER]
                              ),
                [Condition]
                )

    Regards,

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
7 years ago

To debug, I would  try tp split it into 2 (or even 3) measures : 

1) calculate the score

2) do an averagex over that.

 

sometimes this way it is easier to understand what is going on with contexts etc....

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful Individual
7 years ago

The whole point is to work out the calculation first for each enginner for each day and then secondly for all engineers per day so not sure how ALL(day) is going to help.

 

Here is a link to simplified pbix that just has 2 days in it Engineer Utilisation

 

To Pauls point I changed the calcuation to SUMX which I think is only way to break the measure (since calcuation has ot be done for all CALL_NUMBERS on that day for that engineer) and you can see that this is adding up all wrong in the total in the bottom table.

 

What is odd is some combinations of engineers on some days seem fine e.g. top table, but add in Adma and goes wrong. 

 

Change the top table to 23rd and calculation goes wrong gain. Perflexed

 

Thnaks for any help

Mike

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi masplin 

    You may create a condition measure.Then get the Eng Day Score with it.

    Condition =
VAR NewinHours =
    CALCULATE ( [Labour Hours], 'Install Report'[CALL_TYPE] = "NEWIN" )
RETURN
    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, IF ( [Labour Hours] > 0, 1, 0 ) )
            )
        )
    )

    Eng Day Score = 
VAR
NewinHours=CALCULATE([Labour Hours],'Install Report'[CALL_TYPE]="NEWIN")
                              
                            
RETURN
      
          AVERAGEX(
                     SUMMARIZE(
                              'Install Report',
                              DateTable[Day],
                              'Install Report'[ENGINEER]
                              ),
                [Condition]
                )

    Regards,

    • masplin's avatar
      masplin
      Icon for Impactful Individual rankImpactful Individual
      7 years ago

      That's great. So my question is why does this work and not my syntax?

    • masplin's avatar
      masplin
      Icon for Impactful Individual rankImpactful Individual
      7 years ago

      Seems if I put my VAR calaution inside the function it works. So a problem with my VAR clause maybe also needs to a SUMMARIZED version

       

        SUMX(
                     SUMMARIZE(
                              'Install Report',
                              DateTable[Day],
                              'Install Report'[ENGINEER]
                              ),
                    IF(
                        [Newin Jobs]>2,                        
                        5,
                        IF(
                            [Newin Jobs]=2 ||
                            AND([Newin Jobs]=1,CALCULATE([Labour Hours],'Install Report'[CALL_TYPE]="NEWIN")>=7),
                            4,
                            IF(
                                [Newin Jobs]=1 && CALCULATE([Labour Hours],'Install Report'[CALL_TYPE]="NEWIN")<7  && [Labour Hours]>=6,
                                3,
                                IF(
                                    [Labour Hours]>4,
                                    2,
                                    IF(
                                        [Labour Hours]>0,
                                        1,
                                        0
                                ))))))