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!  