Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fellipea_ao
Frequent Visitor

How to calculate Quartiles for two or more months

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

fellipea_ao_0-1668292911702.png


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:

fellipea_ao_4-1668290867781.png

 

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: 

fellipea_ao_3-1668290829977.png

 

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.

fellipea_ao_5-1668290939717.png

 

Link of dataset and powerbi file:

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_1-1668414874226.png

The same to Q2, Q3 and _Quartil_user.

Get the result:

vkalyjmsft_2-1668415105601.png

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.

View solution in original post

4 REPLIES 4
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_1-1668414874226.png

The same to Q2, Q3 and _Quartil_user.

Get the result:

vkalyjmsft_2-1668415105601.png

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.

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

I was looking for a function like "values" 

lbendlin
Super User
Super User

Have you tried removing the 

DF[NM_MONTH]= mes

filter?  That should not really be necessary. 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.