SOLVED

New Contributor

# How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Average?

Hello,

Thank you for your time to review my post.

I have a huge database being supported by pivot tables and pivot charts. The field value used is Average. I am trying to find a way that pivot tables DO NOT consider zeros' in their calculation of average. Is there a way that Pivot table just dynamically omits all zeros'. I am inserting a picture to explain the problem in a simpler manner:

I have already tried to have function NA(), replacing the zeros in the data source, but this will deactivate the pivot table, since data source has NA().

I sincerely appreciate the help from the Excel community.

Thanks

4 Replies
best response confirmed by rahulvadhvania (New Contributor)
Solution

# Re: How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Avera

Creating the data table add data to data model

Result will be

# Re: How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Avera

@Sergei Baklan Thank you so much! This WORKS exactly like I wanted.

However, I am trying to understand the logic for the formula:

 Category Value A 101 A 102 A 0 A 104 A 0 B 106 B 107 B 0 B 109

=CALCULATE(AVERAGE(Table1[Value]),Table1[Value]<>0)

According to my understanding when we expand the logic:

For Category B:

Average((106,107,0,109),(106,107,109))

=92???

Whereas, excel calculates it correctly like I wanted : AVERAGE(106,107,109) = 107.33

# Re: How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Avera

CALCULATE() function evaluates the expresion defined by first parameter AVERAGE(Table1[Value]) in filter context defined by other patameters. Thus we apply to our current context (e.g. table which has all values for Category B if we are within B row of PivotTable) additional filter contexts Table1[Value]<>0, i.e. for category B return all rows for which Value is not equal to zero. Result is {106,107,109} which we average.

# Re: How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Avera

Great. Thanks for the explanation.