cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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)

 

 

 

 

I appreciate your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
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.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

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

View solution in original post

2 REPLIES 2
Highlighted
Community Support
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.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

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

View solution in original post

Highlighted

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors