Limit for Percentile.exc

[Anonymous]

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)

I appreciate your help.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

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.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

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!