Z score to percentiles

[kmoxley]

I'd like to create a collum of percentiles from a collum of z-scores

[v-yulgu-msft · Accepted solution]

Hi @kmoxley,

Suppose there is an existed column [Z-score] in source table, now you want to generate the percentile values based on z-score, right?

If so, please refer to below DAX formulas to create two calculated columns in source table:

Rank =
RANKX ( 'Z-Score', 'Z-Score'[Z-Score],, DESC, DENSE )

Percentile =
DIVIDE (
    CALCULATE (
        COUNT ( 'Z-Score'[Z-Score] ),
        FILTER ( 'Z-Score', 'Z-Score'[Rank] > EARLIER ( 'Z-Score'[Rank] ) )
    ),
    CALCULATE ( COUNT ( 'Z-Score'[Z-Score] ), ALL ( 'Z-Score' ) )
)

If this does not meet your requirement, please share your sample data and your desired result. Also, tell us the syntax, based on which we can calculate the percentiles.

Regards,
Yuliana Gu

[SO]

@v-yulgu-msft, @kmoxley   Thanks so much for this information.  As we work in Live Data 'In Service', the Earlier function does not work for me.  Is there a way to do this without 'Earlier'?  
Curious and many thanks !

[dragonus]

@v-yulgu-msft Is there a way for your measures to apply to a long table? I tried it with a wide table & it worked, but not with a long one.