PERCENTILEX.INC NOT WORKING

[dphillips]

I have a table of data and I have successfully created measures which give an average, max of average, min of average and rank of average, for each student for a certain time period. Please see link to pbix provided.

pbix file

I am trying to also get a decile ranking  for each student - see the measure labelled LPPercentile. (Last column of table)

If a student is in the top 10% based on their LPAve, then I want 1st in the LPPercentile column, top 20% = 2nd etc.

Here is what I have at the moment but it does not appear to be working correctly.

LPPercentile = 
                switch(
                        TRUE(),
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.1),"1st",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.2),"2nd",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.3),"3rd",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.4),"4th",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.5),"5th",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.6),"6th",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.7),"7th",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.8),"8th",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],.9),"9th",
                        [LPAve] <= PERCENTILEX.INC(ALL(uncRedshift_Studentresults),[LPAve],1),"10th"
)

I am not getting the correct distribution of students in decile bands like I would want. There are 227 students in the year group so I would assume around 23 students in each decile but I am getting a lot of 5th and not much of anything else. Basically, in the end, I want to know which students were in the bottom 10% of the cohort based on their LPAve...

Any help would be greatly appreciated.

[v-lili6-msft · Accepted solution]

hi, @dphillips 

If you could try this formula

Measure 4 = 
VAR _table =
    SUMMARIZE ( ALL ( 'Table' ), 'Table'[StudentID], "_Rank", [LPRank] )
VAR _table1 =
    ADDCOLUMNS ( _table, "_Rank1", RANKX ( _table, [_Rank] ) )
VAR Percentile10 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.1 )
VAR Percentile20 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.2 )
VAR Percentile30 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.3 )
VAR Percentile40 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.4 )
VAR Percentile50 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.5 )
VAR Percentile60 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.6 )
VAR Percentile70 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.7 )
VAR Percentile80 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.8 )
VAR Percentile90 =
    PERCENTILEX.INC ( _table1, [_Rank1], 0.9 )
VAR Percentile100 =
    PERCENTILEX.INC ( _table1, [_Rank1], 1 )

RETURN
 IF (
        [LPRank] <= Percentile10,
        "1st",
        IF (
            [LPRank] <= Percentile20,
            "2nd",
            IF ( [LPRank] <= Percentile30, "3rd",
 IF (
        [LPRank] <= Percentile40,
        "4th",
        IF (
            [LPRank] <= Percentile50,
            "5th",
            IF ( [LPRank] <= Percentile60, "6th",
 IF (
        [LPRank] <= Percentile70,
        "7th",
        IF (
            [LPRank] <= Percentile80,
            "8th",
            IF ( [LPRank] <= Percentile90, "9th",





 "10th" )
        )
    ) ))))))

Best Regards,

Lin

[dphillips]

Thanks @v-lili6-msft . That works well. Thanks for your help.