How to calculate Quartiles for two or more months

[fellipea_ao]

Hello!
I'm working with a sample of a dataframe that contains 6 columns, and show the productivity of workers in 6 months. Columns:

NM_MONTH = name month
CD_USER = user code
NM_EQUIP = name of work team
TOTAL_PROD = total of produtivity in a month and equip
WORKED_DAYS = worked days in a month and equip
MEDIAN_PROD = total_prod / workd_days

This sample have just 2 teams, 6 months and  215 workers.
I have to calculate in what quartile (Q1, Q2, Q3 or Q4) is a worker based in their equip, calculating just one month and calculating two or more months.

For just one month, my measure is correctly, but when I try calculate for two or more months, my measure dont work.

Example for cd_user = A33695 nm_equip = N2C caculated in excel using percentil.inc:

In PBI to get the quartile for only one month, I used this measure and worked perfectly:

_Quartil_user = 
 var equipe = LASTNONBLANK(DF[NM_EQUIP], [NM_EQUIP])
 var mes = LASTNONBLANK(DF[NM_MONTH], [NM_MONTH])

 var q1 = 
    calculate(PERCENTILE.INC(DF[MEDIAN_PROD], 0.25), 
        FILTER(ALL(DF),DF[NM_EQUIP] = equipe && DF[NM_MONTH]= mes))
 
 var q2 = 
    calculate(PERCENTILE.INC(DF[MEDIAN_PROD], 0.50), 
        FILTER(ALL(DF),DF[NM_EQUIP] = equipe && DF[NM_MONTH]= mes))

 var q3 = 
 calculate(PERCENTILE.INC(DF[MEDIAN_PROD], 0.75), 
    FILTER(ALL(DF),DF[NM_EQUIP] = equipe && DF[NM_MONTH]= mes))

var prod = divide(sum(DF[TOTAL_PROD]), SUM(DF[WORKED_DAYS]))

 return

    IF(prod <= q1, "Q1",
        IF(prod > q1 && prod <= q2, "Q2",
            IF(prod > q2 && prod <q3, "Q3", "Q4"
            )
        )
    )
  

Result: 

But when I filter 2 months or all period, the measure will not work, because I used a variable that look just the last month and get the results. I tried another ways without this variable, but I failed.

 var mes = LASTNONBLANK(DF[NM_MONTH], [NM_MONTH])

How can I make a measure that ll work when I filter just one month and all period?

Expected result for all 6 months for the same user: the quartile ll be Q2 but in PBI returned Q3.

Link of dataset and powerbi file:

https://drive.google.com/drive/folders/1yvOxKaFXmEPkdZ_RbpbkCwyC44e73G6F?usp=share_link

[v-yanjiang-msft · Accepted solution]

Hi @fellipea_ao ,

According to your description, I download your sample and here's my solution.

_Q1 = 
 var equipe = LASTNONBLANK(DF[NM_EQUIP],[NM_EQUIP])
 var mes = VALUES(DF[NM_MONTH])

 return
    CALCULATE(PERCENTILE.INC(DF[MEDIAN_PROD], 0.25),
        FILTER(ALL(DF),DF[NM_EQUIP] = equipe&&'DF'[NM_MONTH] IN mes))

I modified the below part in the red box.

The same to Q2, Q3 and _Quartil_user.

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fellipea_ao]

Thank you @v-yanjiang-msft! Works perfectly.

I was looking for a function like "values" 

[lbendlin]

Have you tried removing the 

DF[NM_MONTH]= mes

filter?  That should not really be necessary. 

[fellipea_ao]

Hello @lbendlin! Yes, I tried without "mes", but not works.
Thank you for the help 🙂