SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2261447%22%20slang%3D%22en-US%22%3EHow%20to%20let%20Pivot%20Table%20ignore%20ZEROs%20from%20the%20data%20while%20calculating%20Average%20and%20Average%20of%20Average%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2261447%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20time%20to%20review%20my%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20huge%20database%20being%20supported%20by%20pivot%20tables%20and%20pivot%20charts.%20The%20field%20value%20used%20is%20%3CSTRONG%3EAverage.%26nbsp%3B%3C%2FSTRONG%3EI%20am%20trying%20to%26nbsp%3Bfind%20a%20way%20that%20pivot%20tables%20DO%20NOT%20consider%20zeros'%20in%20their%20calculation%20of%20average.%20Is%20there%20a%20way%20that%20Pivot%20table%20just%20dynamically%20omits%20all%20zeros'.%20I%20am%20inserting%20a%20picture%20to%20explain%20the%20problem%20in%20a%20simpler%20manner%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-04-08%20111930.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271105i10B3E83B954EDABB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-04-08%20111930.png%22%20alt%3D%22Screenshot%202021-04-08%20111930.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20already%20tried%20to%20have%20function%20NA()%2C%20replacing%20the%20zeros%20in%20the%20data%20source%2C%20but%20this%20will%20deactivate%20the%20pivot%20table%2C%20since%20data%20source%20has%20NA().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20sincerely%20appreciate%20the%20help%20from%20the%20Excel%20community.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2261447%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2261783%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20let%20Pivot%20Table%20ignore%20ZEROs%20from%20the%20data%20while%20calculating%20Average%20and%20Average%20of%20Avera%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2261783%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1020746%22%20target%3D%22_blank%22%3E%40rahulvadhvania%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreating%20the%20data%20table%20add%20data%20to%20data%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20386px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271140i466A94C02FCC50CB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20add%20measure%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20562px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271141i97B0BAD9AA5641B4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EResult%20will%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20446px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F271142iAE349F4C113E2537%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.