cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
masplin Member
Member

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 score for each day for all engineers, but getting something differnet 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 totla for the day.

 

Eng Day Score = 
VAR
NewinHours=CALCULATE([Labour Hours],'Install Report'[CALL_TYPE]="NEWIN")
                              
                            
RETURN
         
          AVERAGEX(
                     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
                                ))))) 

This is the result

 

Capture.PNG

 

So score for each engineer is right based on my if statement, but total should clearly be 2.5. I tried wrapping the measure in another AVERAGEX summarised jut by day but that makes no differnece.  Any pointers appreciated?

 

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Scratching my head over AVERAGEX

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
v-cherch-msft Super Contributor
Super Contributor

Re: Scratching my head over AVERAGEX

Hi @masplin 

It seems you may use ALL function: ALL(DateTable[Day]) to ignore the filters by date.Reference:https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If you need further help,please share some sample data and expected output which would be helpful to provide an accurate solution.You can also upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PaulDBrown Senior Member
Senior Member

Re: Scratching my head over AVERAGEX

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 Member
Member

Re: Scratching my head over AVERAGEX

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. 

 

Capture.PNG

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

 

Thnaks for any help

Mike

masplin Member
Member

Re: Scratching my head over AVERAGEX

Hi Cherie. As suggested have attached a sample file so any help on this apprecaited as same syntax seems to work fine on other calculations

 

Mike

v-cherch-msft Super Contributor
Super Contributor

Re: Scratching my head over AVERAGEX

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
masplin Member
Member

Re: Scratching my head over AVERAGEX

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

masplin Member
Member

Re: Scratching my head over AVERAGEX

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
                                ))))))

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 165 members 1,845 guests
Please welcome our newest community members: