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.

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!