I want to calculate the percentile of amount based on the amount column in my dataset. The calculation works fine, but when i try to add this column to a table view which has a list of all reports and their corresponding amount values, the percentile changes for each row. I want the percentile to remain same, so i can compare each row amount against this percentile value.
Could someone please help with this scenario.
Here are sample formulae used:
percentile(nonzeroamount,75)
ifelse(
sum(nonzeroamount) >= {percentile_75}, “Greater than 75th percentile”,
sum(nonzeroamount) >= {percentile_50} AND sum(nonzeroamount) < {percentile_75}, “Between 50th and 75th percentile”,
sum(nonzeroamount) >= {percentile_25} AND sum(nonzeroamount) < {percentile_50}, “Between 25th and 50th percentile”,
sum(nonzeroamount) < {percentile_25},“Less than 25th percentile”,
“NA”
)
In my scenario, the percentile is changing for each row, and is coming out as same as the row amount. So all rows are being classified as gretaer than 75.