Dynamic calculation of percentiles

[anandgovindaraj]

Hi, 

Below is an example dataset. A is the number of iterations/simualtions, B is the revenue category and C is the revenue value. 

A    B     C
1    X    23
2    X    21
3    X    23
4    X    25
5    X    26
1    Y    28
2    Y    21
3    Y    23
4    Y    23
5    Y    25
1    Z    24
2    Z    27
3    Z    28
4    Z    29
5    Z    30

There is a slicer for the Category Column 'B' (multiple selections possible). Based on the slicer selection, I would like to calculate the 5th and 95th percentile values of the Revenue (Column 'C')  for the 5 simulations (Column 'A').    
   
If only category is selected, then its 5th and 95th percentile revenue values for that category. If multiple categories  are selected, then the revenue values should be summed (groupby Column A)  and then the 5th and 95th values should be calculated.  
      
Thank you for your time and help!   
  

[OwenAuger · Accepted solution]

Hi @anandgovindaraj

Here are measures that should calculate the percentiles for you (replace YourTable with actual table name):

5th percentile =
PERCENTILEX.INC (
    VALUES ( YourTable[A] ),
    CALCULATE ( SUM ( YourTable[C] ) ),
    0.05
)

95th percentile =
PERCENTILEX.INC (
    VALUES ( YourTable[A] ),
    CALCULATE ( SUM ( YourTable[C] ) ),
    0.95
)

The first argument of PERCENTILEX.INC defines the dimenson over which the percentiles are calculated. In this case, I have used  VALUES ( YourTable[A] ) which effectively groups your data by distinct values in column A.

The second argument defines the values themselves.

Regards,

Owen

[NashDhaqucir]

I have a doubt in that solution. Can we use multiple column values for that?

[OwenAuger]

@NashDhaqucir  can you show an example of what you are trying to do?

To calculate the percentiles over distinct values of multiple columns, you can use SUMMARIZE instead of VALUES.

[NashDhaqucir]

I had the solution for that, anyways thank you @OwenAuger  for your help.

[anandgovindaraj]

Thank you for providing a solution. 

'Values' functionality is exactly what i needed.