cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Limit for Percentile.exc

Hey guys. I need help with calculating percentile.

I have a dataset with the sales for clients, month by month. I want to get a sales benchmark for each client. I defined that the benchmark will be the 4th quartile of sales (percentile of 0.75).

The function percentile in Power BI has a limitation of calculating percentile.exc for a data set with 0.75 <  n/ (n +1).

I know that some client will have a smaller dataset than that. So I calculate the limit and tried to create a check before asking for the percentil.

``````VAR LIMIT = N / (N + 1)

IF(LIMIT > 0.75, PERCENTILE)``````

But even trying to catch the error that way, I'm not being able to eliminate the error. Can you help me please?

This is my code:

``````Benchmark =
VAR CURRENTPERIOD = 'BD PCA'[PERIOD]
VAR COUNTROWS = CALCULATE(COUNT('BD PCA'[VOLUME]), ALLEXCEPT('BD PCA', 'BD PCA'[NR CLIENTE]), 'BD PCA'[Period] <= CURRENTPERIOD, 'BD PCA'[ACTIVITY] = "Active")
VAR SUPERIORLIMIT = DIVIDE(COUNTROWS, (CONTROWS + 1))
VAR Q3 = CALCULATE(PERCENTILE.EXC('BD PCA'[VOLUME], 0.75), ALLEXCEPT('BD PCA', 'BD PCA'[NR CLIENTE]), 'BD PCA'[PERIOD] <= CURRENTPERIOD, 'BD PCA'[ACTIVITY] = "Active")
VAR VOLMAX = CALCULATE(MAX('BD PCA'[VOLUME]), ALLEXCEPT('BD PCA', 'BD PCA'[NR CLIENTE]), 'BD PCA'[Period] <= CURRENTPERIOD)
RETURN IF(SUPERIORLIMIT > 0.75, Q3, VOLMAX)``````

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Hi @victorarnaud,

Maybe you can try to move your calculation into if statement, it will prevent the calculations on the ranges who not suitable the conditions:

``````Benchmark =
VAR CURRENTPERIOD = 'BD PCA'[PERIOD]
RETURN
CALCULATE (
IF (
CALCULATE ( COUNT ( 'BD PCA'[VOLUME] ), 'BD PCA'[ACTIVITY] = "Active" ) > 3,
CALCULATE (
PERCENTILE.EXC ( 'BD PCA'[VOLUME], 0.75 ),
'BD PCA'[ACTIVITY] = "Active"
),
MAX ( 'BD PCA'[VOLUME] )
),
VALUES ( 'BD PCA'[NR CLIENTE] ),
'BD PCA'[Period] <= CURRENTPERIOD
)
``````

If above not help, please share some dummy data to test.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Highlighted
Community Support

Hi @victorarnaud,

Maybe you can try to move your calculation into if statement, it will prevent the calculations on the ranges who not suitable the conditions:

``````Benchmark =
VAR CURRENTPERIOD = 'BD PCA'[PERIOD]
RETURN
CALCULATE (
IF (
CALCULATE ( COUNT ( 'BD PCA'[VOLUME] ), 'BD PCA'[ACTIVITY] = "Active" ) > 3,
CALCULATE (
PERCENTILE.EXC ( 'BD PCA'[VOLUME], 0.75 ),
'BD PCA'[ACTIVITY] = "Active"
),
MAX ( 'BD PCA'[VOLUME] )
),
VALUES ( 'BD PCA'[NR CLIENTE] ),
'BD PCA'[Period] <= CURRENTPERIOD
)
``````

If above not help, please share some dummy data to test.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Frequent Visitor

Thank you, Sheng. This solved the problem!

Can you please explain why using the calculation inside the IF statement is different than using a variable? I thought Power BI only computated the variable once stated.

Another question that I have is: What is the difference to use these lines inside a filter?

``ALLEXCEPT('BD PCA', 'BD PCA'[NR CLIENT])``
``VALUES('BD PCA'[NR CLIENT])``

Thank you very much!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors