Create percentile columns

[PeterChu]

Hi Everyone,

I am trying to create servel percentile column in PowerBi DeskTop.

1. Here is the souce table looks like.Every MemberId has only one row

2. I want to classify each member based on the percentile like this

3. My test file is in googledrive.

https://drive.google.com/drive/folders/1K_U1b1I-FKNm-rNirf-GrooxA4QX3Lme

4. This is my test column function, but the result is included zero

   I need to exclude 0 before doing calculations.

AvgDailyBillinCounts_Flag =
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.9),10,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.8) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.9) ,9,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.7) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.8),8,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.6) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.7),7,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.5) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.6),6,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.4) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.5),5,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.3) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.4),4,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.2) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.3),3,
IF('Flag'[AvgDailyBillinCounts] >= PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.1) && 'Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.2),2,
IF('Flag'[AvgDailyBillinCounts] < PERCENTILE.exc('Flag'[AvgDailyBillinCounts],0.1),1,0))))))))))

Thank you all.

[v-chuncz-msft]

@PeterChu,

This seems to work fine. You may add FILTER Function if necessary.