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 Percentile Rate =
VAR TotalScore =Table1[Score]
RETURN
IF(
HASONEVALUE(Table1[Employee ID]),
COALESCE(
DIVIDE(
--Numerator (below) counts values that are < the EMP's Score in the Employee ID
CALCULATE(
COUNTROWS(Table1),
FILTER(
ALLEXCEPT(Table1,Table1[Employee ID]),
Table1[Score] < TotalScore
)
),
--Denominator (below) counts the total employees in the Employee ID segment
CALCULATE(
COUNTROWS(Table1),
ALLEXCEPT(Table1, Table1[Employee ID])
)
),
0
)
)
9 Replies
Hi Anonymous ,
Replace the HASONEVALUE function like this:
Employee Percentile Rate = VAR TotalScore = Table1[Score] RETURN IF ( Table1[Employee ID] <> BLANK (), COALESCE ( DIVIDE ( --Numerator (below) counts values that are < the EMP's Score in the Employee ID CALCULATE ( COUNTROWS ( Table1 ), FILTER ( ALLEXCEPT ( Table1, Table1[Employee ID] ), Table1[Score] < TotalScore ) ), --Denominator (below) counts the total employees in the Employee ID segment CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Employee ID] ) ) ), 0 ) )Get the result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
Hi Anonymous ,
I can help you, but please let me know which are your expected numbers in Column [Percentile Rate]
Employee IDColumn1ScoreEmployee Percentile Rate
E119848 Capital Projects 18,03 0,666666666666667 E119848 Customer Account Services 3,63 0,333333333333333 E119848 Directors Office 3,44 0 E153665 Capital Projects 12,37 0,666666666666667 E153665 Directors Office 4,13 0,333333333333333 E153665 Houston Permitting Center 4,08 0 E162855 Capital Projects 10,13 0,5 E162855 Customer Account Serivces 7,75 0 E098039 Capital Projects 16,99 0 E155642 Capttal Projects 15,25 0 Do you need something like this?
Employee IDColumn1ScoreEmployee Percentile Rate
E119848 Capital Projects 18,03 1 E119848 Customer Account Services 3,63 0,666666666666667 E119848 Directors Office 3,44 0,333333333333333 E153665 Capital Projects 12,37 1 E153665 Directors Office 4,13 0,666666666666667 E153665 Houston Permitting Center 4,08 0,333333333333333 E162855 Capital Projects 10,13 1 E162855 Customer Account Serivces 7,75 0,5 E098039 Capital Projects 16,99 1 E155642 Capttal Projects 15,25 1 Hi Anonymous
please let us know how is the formula for the percentile and then we provide you a DAX solution.
sorry for the misunderstanding, but I need to know which are your expected values in Column [Employee Percentile Rate]. Can you send us a sample table?
Service Line Incentive Pool Employee ID First Name Last Name Eligibility Factor Employee Percintile Score Customer Account Services CAS001BS E123037 Eric Burton 3.75 Customer Account Services CAS001BS E083568 James Ontiveros 4.63 Customer Account Services CAS001BS E095868 James St. Julien 4.25 Customer Account Services CAS001BS E130370 James Diep 3.76 Customer Account Services CAS001CO E127667 James Artiga 3.82 Customer Account Services CAS001IC E115243 James Adelakoun 4 Customer Account Services CAS001IC E112434 James Menefee - PWE 4.5 Customer Account Services CAS001IC E113468 James Hughes 4.75 Customer Account Services CAS001IC E090711 James Vincent 3.88 Customer Account Services CAS001MR E169863 James McDonald 4.88 Customer Account Services CAS001MR E118293 James Nguyen 4 Capital Projects CIP001CP E160779 Robert Carpy 4.46 Capital Projects CIP001CP E155001 Robert Burroughs 4.5 Capital Projects CIP001CP e172462 Robert Al Amari 3.76 Capital Projects CIP001CP e137368 Robert Hall 4.25 Capital Projects CIP001CP E167678 Robert Sanders 3.88 Capital Projects CIP001CP E168042 Robert Patankar 4 Capital Projects CIP001CP E150729 Robert Dedeaux 4.01 Capital Projects CIP001CP e168057 Robert Lee 4.75 Capital Projects CIP001CP E174429 Robert Burrell 4 Capital Projects CIP001CP E128238 Robert O'Briant 3.75 Capital Projects CIP001CP E137141 Robert Watson 4 Capital Projects CIP001CP E154894 Robert Cantu 4.25 Capital Projects CIP001CP E173676 Robert Gillespie 4 Capital Projects CIP001CP E168593 Robert McKee 5 Capital Projects CIP001CP E172387 Robert Ponton 4 Capital Projects CIP001CP E140164 John Davidson 3.75 Directors Office DIR001LD E110055 John Lopez 3.75 Directors Office DIR001LD E123033 John Vuong 3.76 Directors Office DIR001LD E079892 John Allen 4.12 Directors Office DIR003IR E139390 John Luckenbach 4 Directors Office DIR003IR E166102 John Nelson 4.67 Directors Office DIR003IR E136984 John Wilson 4.12 Directors Office DIR006IT e167581 John Hussain 3.75 Directors Office DIR006IT e167935 John Marcano 4.58
