75th percentile based off measure or distinctrows

[Anonymous]

Hi everyone,

I have a dataset with 17,000 rows. I am trying to calculate the 75th percentile for the amount of hours it took. The only issue is I do not want it based off the entire column as there are duplicate hours.

Quote                                                                        Hours

If I calculated the 75th percentile based off the small sample above, it would return the value using all of the hours above.

I have a measure that adds up the total amount of hours per each quote written here: 

Total Hours = VAR MyTempTable =
    ADDCOLUMNS(
        SUMMARIZE('FY2021 WW3 Quote Status', 'FY2021 WW3 Quote Status'[ Quote],'FY2021 WW3 Quote Status'[Hours Num]),
        "DistinctValue", CALCULATE(MAX('FY2021 WW3 Quote Status'[Hours Num]))
    )
    return 
    SUMX( MyTempTable, [DistinctValue])

The picture below is how I want the 75th percentile to be calulated.

Quote                                                                        Hours

I am not sure how to go about calculating the 75th percentile for the above picture. 

I hope this makes sense and any input would be super helpful!

Thank you!

[mahoneypat · Accepted solution]

You should be able to use an expression like this.

Pct75 Distinct = PERCENTILEX.EXC(SUMMARIZE(Hours, Hours[Quote], Hours[Hours]), Hours[Hours], 0.75)

Pat

[Anonymous]

bump