SOLVED

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

New Contributor

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:

 

Screenshot 2021-04-08 111930.png

 

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

@rahulvadhvania 

Creating the data table add data to data model

image.png

and add measure like

image.png

Result will be

image.png

 

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

 

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

CategoryValue

A

101
A102
A0
A104
A0
B106
B107
B0
B109

 

=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

 

 

 

 

@rahulvadhvania 

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.