Using Aggregate Function excluding zeros and values not change when excluding hidden rows

[romeerome368]

Good Evening,

I saw a recent post on this subject, but it doesn't address my situation specifically.

I have a table that is linked to an Access database, and I'm trying to calculate percentiles using the AGGREGATE function. There are also zeros that I do not want to include in the percentile calculations so I built slicers to help filter the data. My formula is giving the correct percentile number because I checked each of them individually, but as my slicer filters the data, my percentile values do not change.

Below is an example of my formula.

=AGGREGATE(18,5,IF(ABS(qryBenchmarkCalcs[Annual Food Rebate (% of Food Purchases)])>0,ABS(qryBenchmarkCalcs[Annual Food Rebate (% of Food Purchases)])),0.25)

I'm also using absolute value to get rid of the negative numbers, because they cause #NUM! errors. I could put IFERROR() around this cause errors to show up as zeros.

I can't upload the file due to private data, but I have add a screen shot of my sheet unfiltered. Pay attention to the change in the sample size.

Here is a screenshot of the data filtered, and as you can see the values did not change.

Any help with this would greatly appreciated.