cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate 90th percentile with date filter

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.

Thanks

1 ACCEPTED SOLUTION
Solution Sage

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

Proud to be a Datanaut!

5 REPLIES 5
Solution Sage

Hello @sarath5140

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

Proud to be a Datanaut!

Helper I

Hi @LivioLanzo ,

 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

Solution Sage

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

Proud to be a Datanaut!

New Member

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

Helper I

Thank you 🙂 It worked

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.