How to find out Percentile values

[AnandKumar1]

Hi,

I've attached my sample sheet where i want to find percentile .

I've used the calculation=PERCENTILE([% of F by Org],.25). This gives the correct result for one column. But i want to find Percentile (column F) where it should exclude the current row and find the 25th & 75th Percentile. Likewise for next row, exclude the current row value

I've to do this logic in both Alteryx and Tableau.

Please take a look at attached file and let me know if it can be achieved in Alteryx & tableau both.

[atcodedog05]

Hi @AnandKumar1 

I want to ask why do you want to exclude the current row 🤔

[AnandKumar1]

Because the business wanted to know if they exclude one row or ID ,how much will be the Percentile and how much it deviates from original percentile?

[Mario36]

But is there a logic in excluding the row ? 
Is it because the rows are null or zero ?

[AnandKumar1]

There is no logic per se. Whether its 0 or non-zero, just exclude and calculate the percentile.

Even i'm confused on it. Need some more clarity on it from business side. This logic doesn't sound relevant to me.

I'l try to find out some more info.

[Mario36]

Yes please. Some clarity and logic should be helpful to determine your desired output.

[AnandKumar1]

I just got the clarification that for column F percentile we should exclude that current row column D value and take rest.

Ex. for column F2,calculation should be =PERCENTILE(D3:D26,0.25).

for F3,=PERCENTILE(D2:D26,0.25) but excluding D3 and it goes on.

[Mario36]

This is a highly complicated ask and a tricky one too. I would suggest you to use a Multi Row Formula Tool to populate the data of the below row and a Summarize Tool with Group By and Percentile to populate your data.