Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 = (
ID | Cost |
5808650 | 0 |
635243 | 0 |
5808251 | 0 |
131938 | 0 |
5808197 | 0 |
5808626 | 1.49 |
5802881 | 4.76 |
5806682 | 6.16 |
5803349 | 6.41 |
Solved! Go to Solution.
Create a new column like this and try percentile on that
new cost = if([Cost]=0, blank(),[Cost])
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
Thank you for your reply, I've tried this it just gives an output of 1 and 5 only
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
Create a new column like this and try percentile on that
new cost = if([Cost]=0, blank(),[Cost])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |