Forum Discussion
Getting a Pivot to ignore zero values in Top 10 filter
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
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.
- 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.
