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

[mxam]

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]

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]

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

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!