Forum Discussion

riccardotesti76's avatar
riccardotesti76
Regular Visitor
2 years ago

Percentile in a specific range

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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      riccardotesti76
      Regular Visitor
      2 years ago

      Thanks for your reply

      below an example

       

      FWTime

      Column A [FW]Column B [Time]  
      1437.90 Percentile 0.5 FW1289.0885127Percentile when FW<=1
      1320.53 Percentile 0.5 FW2232.5888831Percentile when FW<=2
      1257.65 Percentile 0.5 FW3143.3801389Percentile when FW<=3
      1249.50 Percentile 0.5 FW4139.7399248Percentile when FW<=4
      2215.68    
      2143.48    
      2143.28    
      2139.95    
      3139.53    
      3130.59    
      3125.82    
      3122.80    
      4117.86    
      4111.55    
      4110.81    
      4110.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