Forum Discussion
Solved
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 pivo...
SergeiBaklan 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