Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
This seems to work fine. You may add FILTER Function if necessary.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |