Forum Discussion

Anonymous

Not applicable

3 years ago

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...

Tutorial Requests

v-yanjiang-msft

Community Support

3 years ago

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.

DAX Query.pbix19 KB

Anonymous

Not applicable

3 years ago

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

Anonymous

Not applicable

3 years ago

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
Not applicable
3 years ago
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
)
)

Related content