Getting a Pivot to ignore zero values in Top 10 filter

Occasional Contributor

I have a number of pivots that make up a sales report. 


Within that I have top and bottom 10 filters to focus on the main challenges/opportunities


However, using Top 10 if the sales person (as per the example) has not many customer values that fit the top 10 selection but within that many of the same value (here it is zero) I get overlap issues 


There are 11 of these files for the different teams , so my two current solutions are 1) add lots of empty rows , just in case and avoid overlap error 2) Change from top 10 to value filter of <x>x etc .


Is there any way to get a pivot to take out the zero values in the column 




2 Replies
Open the File Tab>Options>Advanced>scroll down the window until Display Sheet>Uncheck Remove zero with zero values>Ok
I do not have my laptop with me so I'm just sharing this from memory.


Creating the PivotTable you may add data to data model and create DAX measure as

sumB:=VAR sm=SUM(Table1[B])
RETURN IF(sm=0, BLANK(), sm)

for such sample model


apply Top 10 filter to it and set proper sorting.