Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
Thank you @v-yanjiang-msft! Works perfectly.
I was looking for a function like "values"
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 🙂
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |