Percentile of a column dynamically grouped (sum) based on silcer selections

[anandgovindaraj]

Hi, 

Below is a sample of price simulation data. Column A is the number of iterations/simulations,  Column B is the price category and Column C is the price.

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!

[v-shex-msft · Accepted solution]

Hi @anandgovindaraj,

You can write a measure to calculate current price percentage based on category, then drag this measure to visual level filter to filter values between 0.05 and 0.95.

Pecent = 
DIVIDE (
    SELECTEDVALUE ( Table2[C] ),
    SUMX ( FILTER ( ALLSELECTED ( Table2 ), [B] IN VALUES ( Table2[B] ) ), [C] ),
    -1
)

BTW, if you not want to display percentage, you can direct drag measure to visual level filter without add it to visual fields.

Regards,

Xiaoxin Sheng