cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anandgovindaraj Frequent Visitor
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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
)

12.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

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

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
)

12.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |