1 of 1 people found this helpful
try IIF(sum([ Revenue ])>[Revenue_95pc],[Revenue_95pc],sum([Revenue]))
Thanks Dana. Even though it does a good work of validating the formula, it does not really help me. I have already created values for 95 percentile. Now, I need to compare the revenue at each of the rows with this value and replace them with the 95th percentile value if revenue exceeds it. The ultimate aim is to fit the data and take care of the outliers.
I'm not sure what your question is?
Your formula does what you say I think. But if you want to do this on each individual row, I think you either have to do this when you get your data or include the individual identifier for the rows to your level of detail.