I am creating a dashboard of pivots from a fixed data source stored on a server and managed by someone else. Within the dashboards I am creating a number of top 5 filters based on various criteria which I ideally would like to refresh with little involvement once set up.
The problem I have is that in some of the tables there is a large amount of zeros in the calculated field and this is causing the top 5 to be a top 100 or more. Unfortunately I cannot place a copy of the field into the Report filter area (as it is a calculated field) so I cannot deselect the zeros.
Below is an example of an output. In this example I would only like to see the 4 with "QOQ $" values but this may be subject to change if the data is refreshed (so may increase to 5 rows or may decrease to 3, 2, 1 or none).
Source is a sum of the data retrieved from the server whilst
Calc field is the calculated results based on the source data.
Source Source Calc field Calc Field Name Q1 2013 Q2 2013 QOQ $ QOQ % Bob 1 10 9 90.0% Bill 5 10 5 50.0% Jennifer 7 10 3 30.0% Martin 9 10 1 10.0% Philip 0 0 0 0.0% Graham 0 0 0 0.0% Henry 0 0 0 0.0% Grand Total 22 40 18 45.0%
Does anyone have a solution?