Forum Discussion

Erdi88's avatar
Erdi88
Copper Contributor
Jan 22, 2020

Pivot table does not display average correctly

Hello,   I hope someone can help me. I am trying to show an average nightly rate in my pivot table, but it does not display correctly. The difference ranges from £0.50 - £20.00. I get that some of ...
admin
excel
Formulas and Functions
PEDRO_MULLER's avatar
PEDRO_MULLER
Copper Contributor
Jul 28, 2023

SergeiBaklan , Thank you for your insights. I have a situation that I believe is similar to the initial reported case, and I would greatly appreciate your input on it.

 

I have created a PivotTable and noticed that groups 1 and 2 are correct (40% and 85%). However, I am unsure about the value (59%) displayed for the top grouping, which is US (row 6). 

 

 

I was anticipating a 73%, which is meant to indicate individual rows. Additionally, I understand if the average of 63%, which would be calculated using the values of 40% and 85%.

 

Can you help me understand where this value is coming from?

 

Thank you,

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 29, 2023

PEDRO_MULLER 

If you have nothing else under US it shall be as you said

first measure is Average and second one is average of averages:

Average of ABC Groups :=
IF (
    ISFILTERED ( Table3[Item.] ),
    AVERAGE ( Table3[ABC] ),
    AVERAGEX ( VALUES ( Table3[Group] ), CALCULATE ( Table3[Average of ABC] ) )
)

Without the file can't say why 59% appeared.

  • PEDRO_MULLER's avatar
    PEDRO_MULLER
    Copper Contributor
    Jul 31, 2023

    Hi SergeiBaklan,

     

    I have included my spreadsheet for your convenience. Prior to sharing, I performed some cleanup. Then, Earlier today, I made a secondary Pivot Table to confirm that the numbers were consistent with the previous data, and they were.

      

    Please do not hesitate to ask me if you have any questions about this file. I appreciate your support and thank you again for it.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 31, 2023

      PEDRO_MULLER 

      Thank you for the sample file.

      PivotTable calculates correctly average of all records for the given month, and in row Grand Total average for all records for all months. That's not average of averages for each Team Member and for each Role.

      If at the bottom of sample table to add the total as

      =SUBTOTAL(1,[ABC] )

      which is average of ABC ignoring filtered rows, when on filtered table result will be the same as in PivotTable

      or

      With DAX measure we may calculate average of averages for this or that grouping, not sure what is your goal.

Resources