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 ,

 

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!