Calculating 90th percentile and group by column with date filter

[sarath5140]

Hi,

I am stuck with the below problem and need your help and inputs to solve my below problem.

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

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

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

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

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 somepart 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

[v-yuta-msft]

@sarath5140 ,

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 somepart 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.

---

Based on the senario above, you may have added ALLSELECTED(Table[Date]) to apply the date slicer on the result but this filter may be removed by other function though I don't know what function you are using. Maybe the measure should be like pattern below:

Result =
CALCULATE (
    PERCENTILE.INC ( Table[T1], 0.9 ),
    FILTER (
        ALLEXCEPT ( Table, Table[Category] ),
        Table[Date] IN VALUES ( Table[Date] )
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sarath5140]

Hi @v-yuta-msft 

Thanks for your reply.

with your measure I am able to calculate the percentiles but my problem is I should be able to calculate the 90th percentile for my output table Total row as well. Can you help me how to do that?

Thanks

[v-yuta-msft · Accepted solution]

@sarath5140 ,

I am afraid this couldn't be achieved because the total/grand total is just summed up automatically by power bi. So I would suggest you to create an additional measure to calculate the 90% of the result.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.