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