Forum Discussion

Jruiz218's avatar
Jruiz218
Frequent Visitor
6 years ago
Solved

Please Help!!! Grand Total Calculating Wrong Average - Power Pivot

Hello!   Im migrating a report from regular Excel tables/Pivots over to PowerPivot, currently this is my scenario:   In the old version of the report my source data came in the following format: ...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Jruiz218 ,

     

    Based on the data you have send out you need to do the following:

    • Create a measure with the Average of the Hours Handled (this will be used as an intermediat step)
    Measure_Average_Hour_Handle =AVERAGE('Dummy Data'[Hours Handled])
    • Add another measure:
    Average_Hours_Handled =
IF (
    HASONEVALUE ( 'Calendar Resolved'[Date] );
    [Measure_Average_Hour_Handle];
    AVERAGEX (
        SUMMARIZE (
            'Calendar Resolved';
            'Calendar Resolved'[Date];
            'Calendar Resolved'[Week Number]
        );
        [Measure_Average_Hour_Handle]
    )
)

     

    Should work as expected.

Jruiz218's avatar
Jruiz218
Frequent Visitor
6 years ago

 

MFelix  Thank you so much for taking a look at this!

 

I tried creating a new column and using your calculation, but the resul on the Grand Total its exactly the same as for my original calculation.

 

However, now you mention context I noticed the Resolved column might present 3 diff scenarios which might be causing this:

 

A) Resolved value is after Created value (i.e. Resolved: /1/1/2020 13:05:00 / Created: 1/1/2020 12:00:00) the return for this would be a positive value (1:05:00)

B) Resolved value is before Created value  (i.e. Resolved: /1/1/2020 12:00:00 / Created: 1/1/2020 13:05:00) the return for this would be a negative value (this is due a bug on the tool that generates the raw data)


C) Resolved value is Blank (item still open at the time to generate the raw data)

to correct this I had the following adjustment on my original formula 

=IF([Resolved]-[Created]<0,0,[Resolved]-[Created])

this would change any negatie value/blank value to 0, I wonder if would be possible to adjust yours with something simillar to sort out that inconvenience.

Regards,

MFelix's avatar
MFelix
Super User
6 years ago

Hi Jruiz218 ,

 

Try the following calculation below.

 

var Time_Opened = IF(Table[Resolved] - Table[Created] <0 , 0 , Table[Resolved] - Table[Created])
Return
IF(HASONEVALUE(Calendar[WEEKNUMBER]), Time_Opened,AVERAGEX(Calendar,Time_Opened) )

 If this does not work can you share a mockup file please.

  • Jruiz218's avatar
    Jruiz218
    Frequent Visitor
    6 years ago

    MFelix 

     

    I just sent you the link to the report via private message.

     

    Thank you so much for taking a look at it!

    • MFelix's avatar
      MFelix
      Super User
      6 years ago

      Hi Jruiz218 ,

       

      Based on the data you have send out you need to do the following:

      • Create a measure with the Average of the Hours Handled (this will be used as an intermediat step)
      Measure_Average_Hour_Handle =AVERAGE('Dummy Data'[Hours Handled])
      • Add another measure:
      Average_Hours_Handled =
IF (
    HASONEVALUE ( 'Calendar Resolved'[Date] );
    [Measure_Average_Hour_Handle];
    AVERAGEX (
        SUMMARIZE (
            'Calendar Resolved';
            'Calendar Resolved'[Date];
            'Calendar Resolved'[Week Number]
        );
        [Measure_Average_Hour_Handle]
    )
)

       

      Should work as expected.

      • Jruiz218's avatar
        Jruiz218
        Frequent Visitor
        6 years ago

        MFelix Amazin! it worked right away! thank you so much!