DAX Query

[Anonymous]

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

)

)

[mangaus1111]

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?

[Anonymous]

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

[mangaus1111]

Hi @Anonymous 

please let us know how is the formula for the percentile and then we provide you a DAX solution.

[mangaus1111]

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

[mangaus1111]

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

[v-yanjiang-msft]

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 _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Using the Dax Query provided I get 0(s)  where there should be percentile values

[Anonymous]

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

[Anonymous]

Employee Percentile Value =

VAR EligibilityFactor = [Eligibility Factor]

RETURN

IF(

Table1[Eligibility Factor] <> BLANK (),

COALESCE(

DIVIDE(

--Numerator (below) counts values that are < the EMP's Score in the Incentive Pool

CALCULATE(

COUNTROWS(Table1),

FILTER(

ALLEXCEPT(Table1,Table1[Incentive Pool]),

Table1[Eligibility Factor] < Table1[Eligibility Factor]

)

),

--Denominator (below) counts the total employees in the Incentive Pool segment

CALCULATE(

COUNTROWS(Table1),

ALLEXCEPT(Table1, Table1[Incentive Pool])

)

),

0

)

)