Log In
Register
Home
July 28, 2020
Offline
Hi fellow MOTHers! I have a dashboard showing revenue by product and a couple of slicers attached to filter for product and customer. I only want to show data for those product with >0 revenue so I of course I can filter the underlying pivot table for non 0 values, but as soon as you apply a slicer and then clear it my non 0 filter also disappears. Does anyone know of a way of permanently suppressing 0's in a pivot table without using a filter?
Thanks
See Answer
July 16, 2010
Offline
Hi Philippa,
It's not clear what field your Slicer is for, but by default a PivotTable will only show items that have values in the source data. If it is returning zeros then it suggests that you have rows in your source data with zero revenue values against them. If so, I would be removing them from my source data with Power Query before loading the data into Excel for analysing in a PivotTable.
Mynda
July 28, 2020
Offline
Hi Mynda,
Sorry if I wasn't clear. For each product there is data for pipeline, revenue by quarter and total revenue. I have a chart that shows total revenue by product and this will include all the products that have no revenue yet as it will be in say Q4. Other charts on the dashboard include pipeline and revenue split by country and a slicer for the product. So if I choose a product that has no YTD revenue this will be reflected on the YTD chart but show future revenue on another which is fine, but when I remove the filter from the slicer I want the chart that shows YTD revenue for all Products not to show those with $0 as it makes the chart look messy. I can't remove rows with no YTD revenue as the data for pipe and future quarter revenue is required. Is there a way to achieve this?
July 16, 2010
Offline
Hi Philippa,
Create separate PivotTables for each chart so that the chart you don't want zero values displaying doesn't include pipeline data. You can then connect the Product Slicer to all of the PivotTables you want it to filter (right-click the Slicer > Report Connections).
I hope that helps. If not, please supply your file so we can be more specific with our answers.
Mynda
July 28, 2020
Offline
Hi Mynda,
I have mocked up an example, I inherited the data and dashboard so please ignore the way it is laid out and the inconsistencies! On the Dashboard tab I would like to be able to not show any 0 values for the Revenue by Client and RBU Product Revenue charts. For example with filters cleared on the Products slicer the Revenue by Client chart should not contain CompanyF, CompanyM or CompanyN as they have 0 revenue, and the RBU Product Revenue chart should not have Product A or Product 2 for the US.
Is this possible?
July 16, 2010
Offline
Hi Philippa,
Thanks for sharing your file. I can see that the problem is caused by the wrong layout for the source data. i.e. the data is already partially pivoted which has forced you to create a calculated field for the YTD Revenue. Your layout should be in a tabular format.
In the file attached I've used Power Query to unpivot the data and you can see the output on the Tabular Data sheet. I then recreated the two PivotTables and Pivot Charts with a Slicer and you'll see there aren't any zero values in either of the charts.
I hope that points you in the right direction for the future.
Mynda
Answers Post
2 Guest(s)
