Percentile calculation not correct

[DietmarF]

Hello,

I have a table which has one line per Step and a Column for a SUM how often the Step occured.
I am creating a CDF for this table. That works fine.
But I am not able to calculate a P10. The values are not correct.

I tried it in this way.

P10  = CALCULATE(PERCENTILEX.INC(Tabelle1,Tabelle1[Step], 0.5),values (Tabelle1[SUM_Events]))

Table is looking like this

Step	SUM_Events
1	5
2	20
3	40
4	20
5	50

 P10 is with my calculation 3. So the SUM_Events are not used at all for calcualtion.

[SolomonovAnton · Accepted solution]

You're correct — the issue stems from misunderstanding how PERCENTILEX.INC works. This function treats the column values equally and doesn’t weight them based on frequency (your SUM_Events). To calculate P10 (10th percentile) correctly from a cumulative distribution, you need to weight the steps by frequency.

Here’s how you can implement a correct weighted percentile calculation using DAX:

🔧 Step-by-Step Weighted P10 Calculation

1. Create a cumulative table to represent your empirical distribution function (CDF).
2. Calculate the cumulative frequency to identify where the P10 lies.

✅ Correct DAX Measure for Weighted P10

P10_Correct = VAR TotalEvents = SUMX(ALL(Tabelle1), Tabelle1[SUM_Events]) VAR PctTarget = 0.1 * TotalEvents RETURN MINX( FILTER( ADDCOLUMNS( SUMMARIZE(Tabelle1, Tabelle1[Step]), "CumSum", CALCULATE(SUM(Tabelle1[SUM_Events]), FILTER(ALL(Tabelle1), Tabelle1[Step] <= EARLIER(Tabelle1[Step]))) ), [CumSum] >= PctTarget ), Tabelle1[Step] )

Explanation:

• TotalEvents computes total occurrences.
• PctTarget calculates the position where 10% of events lie.
• ADDCOLUMNS + FILTER finds the first step where cumulative events exceed 10% of total.

Optional: You can enhance the precision by interpolating between steps if needed, but this provides the correct step-level P10.

Try using this measure in your report and compare the result with your expected percentile. Let me know if you want to add interpolation for exact value.

✔️ If my message helped solve your issue, please mark it as Resolved!

👍 If it was helpful, consider giving it a Kudos!