Calculate 90th percentile with date filter

[sarath5140]

Hi,

I am reposting this question since i didn't get any response. Please help me to solve my below problem.

I have a table with the below data. Here T1,T2,T3 are in minutes and date column also available.

Based on this table data, I wanted my output to look like this

Example :-

For Category Factory, I wanted to calculate the 90th percentile for T1(m) from TableA data with the fomula like = percentile.Inc([9925,20,301,45],0.90) which gives me the ouput value equals 7037.8 and for Total in the ouput also would like to calculate the 90th percentile for T1(m) equals 5638 using the same formula.

Note :- I need to have the date filter applied on the output table when i select the date range my output values should change accordingly including with my total 90th percentile values as well.

I was able to get the output for some part but my problem is I am unable to apply the date filter. Can anyone please look in to my problem and give me the samples.

Really appreciate your help!

Thanks

[LivioLanzo]

Hello @sarath5140 

are you table to post data which can be copy pasted?

[sarath5140]

Hi @LivioLanzo ,

Please find the below tables.

Category	ID	T1	T2	T3	Date
Factory	1017217	9925	10350	10840	1/25/2019
Factory	771456	20	1127	60	11/10/2018
Factory	223458	301	308	38	2/25/2017
Factory	3456256	45	40	40	9/21/2018
Outlet	224568	1	121	1216	10/29/2018
Outlet	595646	5	690	720	8/6/2018
Outlet	228765	23	130	405	9/12/2018
Support	127896	1	86	621	2/19/2019
Support	1125678	56	5	36	7/21/2018
Support	324567	5	10	612	2/4/2018
Support	228796	1	12	245	12/19/2018

My output should be like :-

Category	ID(count)	T1(m)	T2(m)	T3(m)
Factory	4	7037.8	7583.1	7606
Outlet	3	19.4	578	1116.8
Support	4	40.7	63.8	618.3
Total		5638	6182	6308

And i should be able to apply the date filter and my output should change accordingly. Please let me know if you need anything else.

Thanks

[LivioLanzo · Accepted solution]

Hi @sarath5140 

my advvice is that you normalize the cateogires, probably you could also unpivot the T's columns and normalize those as well but I am not sure what they actually mean. 

Following the below model you can add these 3 measures (if you were to unpivot the T columns only 1 measure would be needed):

T1(m) = 
PERCENTILEX.INC(
    SUMMARIZE(Data, Categories[Category] ),
    CALCULATE( PERCENTILE.INC( Data[T1], 0.9 ) ),
    0.9
)

T2(m) = 
PERCENTILEX.INC(
    SUMMARIZE(Data, Categories[Category] ),
    CALCULATE( PERCENTILE.INC( Data[T2], 0.9 ) ),
    0.9
)

T3(m) = 
PERCENTILEX.INC(
    SUMMARIZE(Data, Categories[Category] ),
    CALCULATE( PERCENTILE.INC( Data[T3], 0.9 ) ),
    0.9
)

Data Model

[KathrineAmby]

Hi!

I have a somewhat similar problem.

The percentile calculation works when adding a date filter and shown in a table. But when creating a visual with the date as the x-axis and the percentile as a line, the percentile is calculated on a daily basis, where it should be a "total" percentile for the time period. If the date filter is removed though, the calculation does not take the time period into consideration.

So do you know if it is possible to create a "total" percentile for a time period, but where it is still possible to change the time period for the calculation. 

Thanks,

Kathrine

[sarath5140]

Hi @LivioLanzo 

Thank you 🙂 It worked