Forum Discussion
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
12 Replies
- SergeiBaklanDiamond Contributor
- willi1950Copper Contributor
SergeiBaklan i have a similar issue but my data is grouped ( by 5 days ), so if i add the data to a data model, although it lets me set up a measure as per your previous reply. This disables the grouping.
- SergeiBaklanDiamond Contributor
With data model grouping is based on calculated columns. If to select, for example, group by month, Power Pivot automatically creates calculated column as
=FORMAT([Date], "MMM")
For grouping by dates parameters are disabled for such case, as for any other non-standard grouping. However, we may create such calculated column ourselves, like
= VAR startDate = DATE ( 2022, 5, 6 ) VAR step = 5 VAR isInPeriod = MOD ( Table1[Date] - startDate, step ) VAR periodNo = INT ( ( Table1[Date] - startDate ) / step ) VAR startPeriod = startDate + step * periodNo VAR endPeriod = startDate + step * ( periodNo + 1 ) - 1 RETURN FORMAT ( startPeriod, "yyyy-mm-dd" ) & " - " & FORMAT ( endPeriod, "yyyy-mm-dd" )
and use it in PivotTable.
- Saron12Copper Contributor
SergeiBaklan I couldn't use the formula to exclude the Zero, No option to include the data Model
- SergeiBaklanDiamond Contributor
What is your Excel version and platform?
- rahulvadhvaniaCopper Contributor
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
- SergeiBaklanDiamond Contributor
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.