Dec 02 2021 11:36 AM
Hi, I'm working on a culture survey analysis for a client and I'm facing two things to solve. The first one is with a filtered table, for confidentiality I would like the results to be filtered if they'll be showing less than 10 rows/results. The second thing is similar (at least sounds that way) I have a dashboard with pivot charts and would like to limit the slicers results too (don't show if information is less than 10 rows/results).
Can this be done?
need info/help ASAP
Thank you beautiful people!
Using Mac BigSur 11.2.3 (20D91) and Excel 16.55
Dec 04 2021 10:47 AM
Hi @NorbertoG
With a helper column like Display below you can mark those not to be disclosed;
=IF(COUNTIFS([Country],[@Country])<10,"********",[@Country])
Use Power Query (Data: Get & Transform) to load that data table into a new table and exclude the records not to be disclosed.
Use the new table as source for your pivot table and insert a new slicer.
Dec 04 2021 11:34 AM
Afraid Power Query won't work on Mac.
Dec 04 2021 10:53 PM
SolutionAh, thank you @Sergei Baklan :)
Then the data source may be dynamic, avoiding 'blanks' in the pivot table, by using a named range that adjusts to the filtered data by referring to
=OFFSET($P$5,0,0,ROWS($P$5#),COLUMNS($P$5#))
where P5
=FILTER(Table1[[#All];[Date]:[Amount]];Table1[[#All];[Display]]<>"********")
Dec 08 2021 10:17 AM
Dec 08 2021 12:12 PM
Jun 30 2022 02:08 PM
Dec 04 2021 10:53 PM
SolutionAh, thank you @Sergei Baklan :)
Then the data source may be dynamic, avoiding 'blanks' in the pivot table, by using a named range that adjusts to the filtered data by referring to
=OFFSET($P$5,0,0,ROWS($P$5#),COLUMNS($P$5#))
where P5
=FILTER(Table1[[#All];[Date]:[Amount]];Table1[[#All];[Display]]<>"********")