Forum Discussion
DAX Query
I am trying to write a dax query to calculate percentile for a value in a sub-group. Below is my query. When I run it the field for Employee Percentile Rate returns blanks. Employee Perc...
Using the Dax Query provided I get 0(s) where there should be percentile values
Using the Dax Query provided I get 0(s) where there should be percentile values
Expected values like this
| Incentive Pool | Employee ID | Employee First Name | Employee Last Name | Eligibility Factor | Employee Percentile Score | |
| CAS001BS | E083568 | Roland | Ontiveros | 4.01 | 50.0% | |
| E095868 | Clayton | St. Julien | 0 | 0.0% | ||
| E122857 | Olivia | Andrews | 0 | 0.0% | ||
| E123037 | Eric | Burton | 4.01 | 50.0% | ||
| E130370 | Sandy | Diep | 0 | 0.0% | ||
| e157644 | Sheldon | Dundy | 0 | 0.0% | ||
| CAS001BS Total | 8.02 | |||||
| CAS001CO | E115990 | Sandra | McDaniels | 3.92 | 48.3% | |
| E127667 | Yasmin | Artiga | 4.19 | 51.7% | ||
| CAS001CO Total | 8.11 | |||||
| CAS001IC | E090711 | Victor | Vincent | 3.81 | 49.5% | |
| E112434 | Marlon | Menefee - PWE | 0 | 0.0% | ||
| E113468 | Stacy | Hughes | 3.88 | 50.5% | ||
| E115243 | Hubert | Adelakoun | 0 | 0.0% | ||
| E125206 | Daniel | Saenz | 0 | 0.0% | ||
| e156425 | Nelson | Bernard Jr. | 0 | 0.0% | ||
| CAS001IC Total | 7.69 | |||||
| CAS001MR | E063977 | I | Williams | 3.76 | 19.1% | |
| E094623 | Ronald | Charles | 0 | 0.0% | ||
| E094629 | Mygonne | Gabriel | 3.88 | 19.7% | ||
| E095362 | Kevin | Mosley | 0 | 0.0% | ||
| E105324 | Joe | Garcia | 0 | 0.0% | ||
| E106179 | Patricia | Smith | 0 | 0.0% | ||
| E111102 | Dracy | Upson | 0 | 0.0% | ||
| E113112 | Keven | Keys | 0 | 0.0% | ||
| E113574 | Armando | Munoz | 0 | 0.0% | ||
| E115035 | Paulette | Lockridge | 4 | 20.4% | ||
| E118293 | Dat Tan | Nguyen | 4.13 | 21.0% | ||
| E119019 | Macario | Andrio | 3.88 | 19.7% | ||
| E120112 | Viet | Duong | 0 | 0.0% | ||
| CAS001MR Total | 19.65 |
- Employee Percentile Value =VAR EligibilityFactor = [Eligibility Factor]RETURNIF(Table1[Eligibility Factor] <> BLANK (),COALESCE(DIVIDE(--Numerator (below) counts values that are < the EMP's Score in the Incentive PoolCALCULATE(COUNTROWS(Table1),FILTER(ALLEXCEPT(Table1,Table1[Incentive Pool]),Table1[Eligibility Factor] < Table1[Eligibility Factor])),--Denominator (below) counts the total employees in the Incentive Pool segmentCALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1, Table1[Incentive Pool]))),0))