Hi, i have a pivot table of staff budget and vacancies but want to exclude departments with a budget less than 10. Using the filter on the row labels this does what i want for all but one pivot table where budget = 9.4 and vacancies = 5.4 (so the sum of data is more than 10 and the row is included).
In a hurry so have edited just this one table to row value >=20 (which is fine), and flagged it for next time, but would like to know if it's possible to only filter on the one column so it works automatically.
Copy of the pivot table attached.
Thanks
The file you uploaded seems to have a values only version of the pivot table, including all departments. In order to look into your problem, I recreated a small data set that would generate such a table. See attached.
Then, by using Power Pivot and some DAX measures you can achieve what you describe. Though, the filtered value of >= 10 is not dynamic. It's hard-coded in the measures. I'm not sure this is a feasible solution for all your other Pivot tables.
Let me know if this is something you can work with.
Thanks Riny, I couldn't use the 'real' pivot table as it includes confidential data, but hoped it could be followed.
I will have a look at your option later - madly trying to finish something else today before a Christmas break - but I don't know anything about DAX.
Depending on your end goal, a quick method is to use an autofilter on the sheet and simply hide rows that way.
Thanks Velouria, not seen (or used) autofilter before so will have a look at that, thanks.
