Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |