Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sarath5140
Helper I
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.

 

enter image description here

 

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

 

enter image description here

 

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

1 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
)

 

2019-03-11_9-45-10.jpg

 

 

Data ModelData Model

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

5 REPLIES 5
LivioLanzo
Solution Sage
Solution Sage

Hello @sarath5140 

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo ,

 

Please find the below tables.

 

CategoryIDT1T2T3Date
Factory1017217992510350108401/25/2019
Factory7714562011276011/10/2018
Factory223458301308382/25/2017
Factory34562564540409/21/2018
Outlet2245681121121610/29/2018
Outlet59564656907208/6/2018
Outlet228765231304059/12/2018
Support1278961866212/19/2019
Support1125678565367/21/2018
Support3245675106122/4/2018
Support22879611224512/19/2018

 

My output should be like :-

 

CategoryID(count)T1(m)T2(m)T3(m)
Factory47037.87583.17606
Outlet319.45781116.8
Support440.763.8618.3
Total 563861826308

 

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

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
)

 

2019-03-11_9-45-10.jpg

 

 

Data ModelData Model

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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

Hi @LivioLanzo 

 

Thank you 🙂 It worked

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors