Percentile in a specific range

[riccardotesti76]

Good morning

I have a table with two column number

- one column is the value that I will use for calculate the percentile value

- one column is the fiscal week

I would like to trend the percentile value in a chart with below condition

In the axis I have FW numbers where

FW1 is the percentile calc for the value that we have FW=1

FW2 is the percentile calc for the value that we have FW<=2

FW3 is the percentile calc for the value that we have FW<=3...

How I can create this? I need to create a separated table?

I noted the PERCENTILEX.INC but I'm not sure about the expression that I need to use...

Thanks

Regards

Riccardo

[v-xuxinyi-msft]

Hi @riccardotesti76 

Do you need to display multiple formulas on a single chart? In order to help you better, could you please provide some sample data and the expected results based on these sample data? That would be very helpful.

My suggestions so far are as follows for your reference:

First, calculate the percentile value corresponding to FW=1, FW<=2, and FW<=3, and then use the following formula:

Measure = 
SWITCH(TRUE(),
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW=1"), [FW=1 formula],
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW<=2"), [FW<=2 formula],
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW<=3"), [FW<=3 formula])

Best Regards,
Yulia Xu

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

[riccardotesti76]

Thanks for your reply

below an example

FWTime

Column A [FW]	Column B [Time]
1	437.90		Percentile 0.5 FW1	289.0885127	Percentile when FW<=1
1	320.53		Percentile 0.5 FW2	232.5888831	Percentile when FW<=2
1	257.65		Percentile 0.5 FW3	143.3801389	Percentile when FW<=3
1	249.50		Percentile 0.5 FW4	139.7399248	Percentile when FW<=4
2	215.68
2	143.48
2	143.28
2	139.95
3	139.53
3	130.59
3	125.82
3	122.80
4	117.86
4	111.55
4	110.81
4	110.77

For calculate of the percentile I used the below formula

Percentile 0.5 FW1 =PERCENTILE.INC(B2:B5;0.5)

Percentile 0.5 FW2 =PERCENTILE.INC(B2:B9;0.5)

Percentile 0.5 FW3 =PERCENTILE.INC(B2:B13;0.5)

Percentile 0.5 FW4 =PERCENTILE.INC(B2:B17;0.5)

I used in PowerBI the formula

Misura = SELECTEDVALUE([FW] = 1 , PERCENTILE.INC([Time], 0.5))

but it's not working...

Thanks

Regards

Riccardo