Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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!