Forum Discussion
How to let Pivot Table ignore ZEROs from the data while calculating Average and Average of Average?
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.
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.
SergeiBaklan I couldn't use the formula to exclude the Zero, No option to include the data Model
What is your Excel version and platform?
- Office 365 Web
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
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.
- Great. Thanks for the explanation.
