# Pivot Table to Include only Amounts Greater Than 0 in Sum of Amount Value Column



## mxam (Oct 25, 2012)

Hi!  I have a spreadsheet I am reporting Attendance for each ticket type each month by Quantity and Dollars.  

The rows are attendance by attendance type.  
The columns are by month with a column for each month for Quantity and Dollars.  

The Problem:  The Quantity amounts are off because it is including the counts for $0 Amounts which I want to exclude.  

I can get the correct quantity amounts by using Dollars as a Report Filter, but I need the Quantity and Dollars for each month side by side.

Is there a way to have the Sum of Quantity column only count the quantities for Amounts greater than $0?

Thank you for your help!!!


----------



## Mavericks334 (Oct 26, 2012)

Use Calculate function, where you can filter out the zeros

=Calculate(Sum[Dollars],[Quantity]<>0)
=Calculate(Count[Quantity],<>[Dollars]<>0)

Use something like the above DAX Formulas


----------



## sullivanj2 (Sep 5, 2013)

Mavericks334 said:


> Use Calculate function, where you can filter out the zeros
> 
> =Calculate(Sum[Dollars],[Quantity]<>0)
> =Calculate(Count[Quantity],<>[Dollars]<>0)
> ...





Hi Mavericks334,

Where in the pivot table function would you enter the above formula?  I tried looking in the Value Field Settings but there isn't a field to enter a custom formula.  Thanks in advance!


----------

