SOLVED

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

Copper 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

12 Replies
best response confirmed by rahulvadhvania (Copper 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

@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

# 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.

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

@SergeiBaklan I couldn't use the formula to exclude the Zero, No option to include the data Model

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

What is your Excel version and platform?

Office 365 Web

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

You need Windows Desktop version of Excel to create data model.

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

@SergeiBaklan
Is there a way to do this for just a specific column of a pivot table? I have a table with 14 columns but I only need 2 of the columns to calculate a zero omitted avg. The column is being calculated with an IF formula in my raw data. I have attached a screen shot of both my raw data calculation for that column and the Pivot Column (had to black out somethings for privacy)

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

You may create as many measures as necessary each with different formula and show them in separate columns. If that's what you mean. Or you'd like not to show dates where average is zero?

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

@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.

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

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.

1 best response

Accepted Solutions
best response confirmed by rahulvadhvania (Copper 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