I have a table as attached. I want to apply conditional formatting based on percentile with the following rule: All positive and negative values should be divided in 30%, 30% and 40% values separately.
I tried applying Window_percentile function, but it evaluates result with +ve and -ve value together. However, my requirement is calculation to be done separately for positive and negative values.
Here is the syntax which resulted in incorrect result.
if (Sum([Incr-Format]) <= WINDOW_PERCENTILE(Sum([Incr-Format]), 0.3)) and Sum([Incr-Format]) >0 then str(WINDOW_PERCENTILE(Sum([Incr-Format]), 0.3)))