Calculate the percentile for Cumulative total

[zaidmasad]

Hello,

How can I calculate the 70th percentile for a cumulative data? My data looks as below:

I need to have for each country the 70th percentile ( how many attempt it takes to deliver 70% of the shipments)

Thanks,

Zaid

[zaidmasad]

@v-xjiin-msft thanks for your reply,

I have created this table from the data, and highlighted the value greater than 70%:

Now I need to find the exact number of how many attempts it takes 70% of the shipments.. Taking Belgium as an example, 70% percent of the shipment have between 0 and 1 attempts, I need to find that exact number of attempts.

In manual calculation to find the percentile what you do is:

P70 = (70/100)*Total number of records

Then you do interpolation to find the exact number.

Here is the finction I have used to find the cumulative percentage:

RunningTotal = 
CALCULATE (
 SUM ( Sheet1[Shipment Count] ),
 FILTER ( ALL ( Sheet1 ), Sheet1[Total Delivery Attempts] <= MAX ( Sheet1[Total Delivery Attempts] ) ),
 VALUES ( Sheet1[Destination Country] ),VALUES(Sheet1[Is Ecom]),VALUES(Sheet1[Customer Account Type])
)

The data I have looks as below:

[v-xjiin-msft]

@zaidmasad

Check this:

My sample data is like

Then to find the 70% percent of the shipment, I am checking the min Total Delivery Attempts of the Attempts over than 70%. Then we just need to count the records which Total Delivery Attempts <= the min Total Delivery Attempts.

MIN >70 Attempts =
CALCULATE (
    MIN ( Sheet1[Total Delivery Attempts] ),
    FILTER ( ALL ( Sheet1 ), Sheet1[RunningTotal] >= 70 ),
    VALUES ( Sheet1[Destination Country] ),
    VALUES ( Sheet1[Is Ecom] ),
    VALUES ( Sheet1[Customer Account Type] )
)

P70 =
CALCULATE (
    COUNTROWS ( Sheet1 ),
    FILTER ( ALL ( Sheet1 ), Sheet1[Total Delivery Attempts] <= [MIN >70 Attempts] ),
    VALUES ( Sheet1[Destination Country] ),
    VALUES ( Sheet1[Is Ecom] ),
    VALUES ( Sheet1[Customer Account Type] )
)

The result shows like this:

If my method doesn't satisfy your requirement, please kindly share us some more detailed sample data ( which we can copy and paste ). So that we can make proper tests.

Thanks,
Xi Jin.

[zaidmasad]

Hi @v-xjiin-msft,

Yes, this is what I want. But I think there is something wrong in the calculations, shouldnt it be the 70th percentile as per you data 1 not 2, right?

Destination Country	Is Ecom	Customer Account Type	Total Delivery Attempts	Shipment Count	Cumulative
Belgium	Is Ecom	Epayment	0	2	2.02%
Belgium	Is Ecom	Epayment	1	79	81.82%
Belgium	Is Ecom	Epayment	2	11	92.93%
Belgium	Is Ecom	Epayment	3	5	97.98%
Belgium	Is Ecom	Epayment	4	1	98.99%
Belgium	Is Ecom	Epayment	5	0.7	99.70%
Belgium	Is Ecom	Epayment	6	0.2	99.90%
Belgium	Is Ecom	Epayment	7	0.1	100.00%
Belgium	Is Ecom	Epayment	8	0	100.00%
Belgium	Is Ecom	Epayment	9	0	100.00%
Belgium	Is Ecom	Epayment	10	0	100.00%
Belgium	Is Ecom	Epayment	11	0	100.00%
Belgium	Is Ecom	Epayment	12	0	100.00%

Also, I am facing a problem when the total delivery attempt is not repeated  in all of the groups, such as the below:

Is Ecom Destination Region Destination Country Customer Account Type Total Delivery Attempts Shipment Count

B2C	Middle East	Saudi Arabia	COD	0	37423
B2C	Middle East	Saudi Arabia	COD	1	3631
B2C	Middle East	Saudi Arabia	COD	2	441
B2C	Middle East	Saudi Arabia	COD	3	27
B2C	Middle East	Saudi Arabia	COD	4	3
B2C	Middle East	Saudi Arabia	Epayment	0	37351
B2C	Middle East	Saudi Arabia	Epayment	1	11985
B2C	Middle East	Saudi Arabia	Epayment	2	625
B2C	Middle East	Saudi Arabia	Epayment	3	26
B2B	Middle East	Saudi Arabia	null	0	50065
B2B	Middle East	Saudi Arabia	null	1	2967
B2B	Middle East	Saudi Arabia	null	2	210
B2B	Middle East	Saudi Arabia	null	3	14
B2B	Middle East	Saudi Arabia	null	4	1
B2B	Middle East	Saudi Arabia	null	0	13552
B2B	Middle East	Saudi Arabia	null	1	1221
B2B	Middle East	Saudi Arabia	null	2	71
B2B	Middle East	Saudi Arabia	null	3	5

for the above, when it counts the cumulative for total delivery attempts "4", the numbers gets wrong, because the group "4" doesnt exist in all categories.

Thanks,

Zaid

[v-xjiin-msft]

Hi @zaidmasad,

Sorry for delayed reply.

=> Taking Belgium as an example, 70% percent of the shipment have between 0 and 1 attempts, I need to find that exact number of attempts.

I thought it means the 70th percentile means 0 and 1. If it is just 0. You can modify the expression to:

P70 =
CALCULATE (
    COUNTROWS ( Sheet1 ),
    FILTER ( ALL ( Sheet1 ), Sheet1[Total Delivery Attempts] < [MIN >70 Attempts] ),
    VALUES ( Sheet1[Destination Country] ),
    VALUES ( Sheet1[Is Ecom] ),
    VALUES ( Sheet1[Customer Account Type] )
)

Then for your second question, what did you mean you want to count the cumulative for total delivery attempts "4". Could you please share us your measure expression? And your shared sample data is a little werid. So for a same Is Ecom, Destination Region , Destination Country, Customer Account Typ, there will exists duplicated Total Delivery Attempts. Right? Like 

B2B

Middle East

Saudi Arabia

null

Thanks,
Xi Jin.

[v-xjiin-msft]

Hi @zaidmasad,

I'm not quite understand about the 70th percentile for a cumulative data. What does this 70th percentile mean? What's the logic to calculate this percentile?

Since you have shared some sample data. What's your desired result based on this sample data?

Thanks,
Xi Jin.