Home

pivot table counting zero

%3CLINGO-SUB%20id%3D%22lingo-sub-883227%22%20slang%3D%22en-US%22%3Epivot%20table%20counting%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-883227%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20use%20the%20count%20function%20in%20a%20pivot%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20to%20not%20count%20zeros!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-883227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-883384%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20counting%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-883384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417582%22%20target%3D%22_blank%22%3E%40David1955%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20use%20CounfIf%20function.%3C%2FP%3E%3CP%3ESo%20if%20you%20are%20counting%20the%20values%20in%20column%20A%20which%20are%20not%20zeros%2C%20you%20may%20try...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(A%3AA%2C%22%26gt%3B0%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20if%20you%20want%20to%20count%20in%20the%20Pivot%20Table%20itself%2C%20while%20inserting%20the%20Pivot%20Table%2C%20check%20the%20box%20for%20%22Add%20this%20data%20to%20the%20Data%20Model%22%20and%20then%20create%20a%20Measure%20to%20count%20except%20zeros%20using%20of%20the%20following%20DAX%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22Count%20%26gt%3B0%22%3CBR%20%2F%3E%3CSTRONG%3E%3DCALCULATE(COUNTROWS(Table1)%2CTable1%5BQty%5D%26gt%3B0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22Count%20%26gt%3B0%22%3CBR%20%2F%3E%3CSTRONG%3E%3DCALCULATE(COUNT(Table1%5BQty%5D)%2CTable1%5BQty%5D%26gt%3B0)%3C%2FSTRONG%3E%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%20style%3D%22width%3A%20386px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134193iC9BE5954D178F46F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Add%20data%20to%20Data%20Model.jpg%22%20title%3D%22Add%20data%20to%20Data%20Model.jpg%22%20%2F%3E%3C%2FSPAN%3E%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%20style%3D%22width%3A%20530px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134194i054D0231AADF38A6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Count%20greater%20than%200.jpg%22%20title%3D%22Count%20greater%20than%200.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
David1955
Occasional Visitor

I want to use the count function in a pivot table

 

but to not count zeros!

 

Help

1 Reply

@David1955 

You may use CounfIf function.

So if you are counting the values in column A which are not zeros, you may try...

=COUNTIF(A:A,">0")

 

Or if you want to count in the Pivot Table itself, while inserting the Pivot Table, check the box for "Add this data to the Data Model" and then create a Measure to count except zeros using of the following DAX formula.

 

"Count >0"
=CALCULATE(COUNTROWS(Table1),Table1[Qty]>0)

 

"Count >0"
=CALCULATE(COUNT(Table1[Qty]),Table1[Qty]>0)

 

Add data to Data Model.jpg

 

Count greater than 0.jpg

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies