Excluding Zeros from percentile calculation

[Kiro_2112]

Good day, 

I was hoping someone could help me, if its possible, to find a percentile excluding the zeros. The reason for this is - in certain columns I have a lot of zeros and would like to calculate the percentile without creating new tables without the zeros.

Below is the the calculations I currently have, and Sample data.

Thank you in advance!!!!:) 

Cost Score = (

Var perc1= PERCENTILE.INC('Table'[Cost], 0.20)

Var perc2= PERCENTILE.INC('Table'[Cost], 0.40)

Var perc3= PERCENTILE.INC('Table'[Cost], 0.60)

Var perc4= PERCENTILE.INC('Table'[Cost], 0.80)

var average_per_hosp = CALCULATE(AVERAGE('Table'[Cost]),ALLEXCEPT('Table','Table'[ID]))

return(

IF(average_per_hosp<=perc1,1,

if(average_per_hosp>perc1 && average_per_hosp<=perc2, 2,

IF(average_per_hosp>perc2 && average_per_hosp<=perc3, 3,

IF(average_per_hosp>perc3 && average_per_hosp<=perc4,4,5)

)

)

)

))

ID	Cost
5808650	0
635243	0
5808251	0
131938	0
5808197	0
5808626	1.49
5802881	4.76
5806682	6.16
5803349	6.41

[v-gizhi-msft]

Hi,

Please change your original measure to this:

Cost Score = 
Var perc1= CALCULATE(PERCENTILE.INC('Table'[Cost], 0.20),'Table'[Cost]>0)
Var perc2= CALCULATE(PERCENTILE.INC('Table'[Cost], 0.40),'Table'[Cost]>0)
Var perc3= CALCULATE(PERCENTILE.INC('Table'[Cost], 0.60),'Table'[Cost]>0)
Var perc4= CALCULATE(PERCENTILE.INC('Table'[Cost], 0.80),'Table'[Cost]>0)
var average_per_hosp = CALCULATE(AVERAGE('Table'[Cost]),ALLEXCEPT('Table','Table'[ID]))
return
IF(average_per_hosp<=perc1,1,
if(average_per_hosp>perc1 && average_per_hosp<=perc2, 2,
IF(average_per_hosp>perc2 && average_per_hosp<=perc3, 3,
IF(average_per_hosp>perc3 && average_per_hosp<=perc4,4,5))))

The result shows:

Hope this helps.

Best Regards,

Giotto

[Kiro_2112]

Thank you for your reply, I've tried this it just gives an output of 1 and 5 only

[Pragati11]

Hi @Kiro_2112 ,

The only way I can think of right now is before doing any Percentile calculation on your data, create a sub-table, which has data only with non-zero value in the "COST" column of your table. The you can use your percentile calculation on this new table.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[amitchandak · Accepted solution]

Create a new column like this and try percentile on that

new cost = if([Cost]=0, blank(),[Cost])