Calculate Median for different weeks in one column

[KiPa]

Hi All

I tried to search but as I am newbie here it is still difcicult to understand complex topics, so I would like to share the full scenario to get it right.

I need to calculate performance of each employee and compare it to the team median weekly.

In Excel I have easy one, but power BI keeps throwing me errors, could you please check and advise?

Thank you so much

KiPa 

Column A is Employee ID

Coulmn B is week name

Column C is calculation - SUM of defined KPIs

Column D is team median for each week

Column E is the final result - comparison of individual result to team median result for each of the weeks

Employee ID	Week name	Index = calculated column for each employee	Weekly median index	Final employee's index in the week
A	W01	12.75	15.19	83.92
B	W01	14.45	15.19	95.14
C	W01	12.71	15.19	83.69
D	W01	18.66	15.19	122.83
E	W01	27.20	15.19	179.09
F	W01	15.92	15.19	104.86
A	W02	18.83	19.70	95.60
B	W02	0.00	19.70	0.00
C	W02	20.56	19.70	104.40
D	W02	20.70	19.70	105.10
E	W02	2.40	19.70	12.18
F	W02	29.36	19.70	149.05
A	W03	30.05	27.03	111.17
B	W03	25.96	27.03	96.03
C	W03	0.00	27.03	0.00
D	W03	23.98	27.03	88.71
E	W03	31.11	27.03	115.09
F	W03	28.10	27.03	103.97

[lbendlin · Accepted solution]

see attached

[KiPa]

Hello @lbendlin 

thanks for the formula, it really works well - untill I use the slicer.

Meaning if I filter by employee ID it will recalculate the team weekly median.

An then the ratio which is the result will change too.

Is there any other way please?

Thank you

ki

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[KiPa]

Thank you,

the original data:

Employee ID	Week name	Index = calculated column for each employee	Weekly median index	Final employee's index in the week
A	W01	12.75	15.19	83.92
B	W01	14.45	15.19	95.14
C	W01	12.71	15.19	83.69
D	W01	18.66	15.19	122.83
E	W01	27.20	15.19	179.09
F	W01	15.92	15.19	104.86
A	W02	18.83	19.70	95.60
B	W02	0.00	19.70	0.00
C	W02	20.56	19.70	104.40
D	W02	20.70	19.70	105.10
E	W02	2.40	19.70	12.18
F	W02	29.36	19.70	149.05
A	W03	30.05	27.03	111.17
B	W03	25.96	27.03	96.03
C	W03	0.00	27.03	0.00
D	W03	23.98	27.03	88.71
E	W03	31.11	27.03	115.09
F	W03	28.10	27.03	103.97

The formula you advised indeed calculates the weekly median for the whole team. However if I want to filter out some team members or if I want to focus on one individual, the team median gets recalculated for each scenario. Using slicer.

Similar like this - removing emplyee A and C will bring new medians, which is not what I need, the expectations should be the same for everyone.

Employee ID	Week name	Index = calculated column for each employee	Weekly median index	Final employee's index in the week
B	W01	14.45	17.29	83.57
D	W01	18.66	17.29	107.89
E	W01	27.20	17.29	157.32
F	W01	15.92	17.29	92.11
B	W02	0.00	11.55	0.00
D	W02	20.70	11.55	179.26
E	W02	2.40	11.55	20.78
F	W02	29.36	11.55	254.20
B	W03	25.96	27.03	96.03
D	W03	23.98	27.03	88.71
E	W03	31.11	27.03	115.09
F	W03	28.10	27.03	103.97

thank you, ki

[lbendlin]

Make it a calculated column if you don't want it to be impacted by filters.

[KiPa]

I am Sorry I think I wasnt clear

I need to have weekly median that remains the same also after filtering the empoyee id - so I can review the data for individual, or half of the team.

The formula provided works well while counting for the whole team and particular week, but any filtering changes the weekly median.

Altogeter: 

Correct without any filtes, using the "med = CALCULATE(MEDIANX('Table',[Index]),REMOVEFILTERS('Table'[Employee ID]))"
Employee ID	Week name	Index = calculated column for each employee	Weekly median index	Final employee's index in the week
A	W01	12.75	15.19	83.92
B	W01	14.45	15.19	95.14
C	W01	12.71	15.19	83.69
D	W01	18.66	15.19	122.83
E	W01	27.20	15.19	179.09
F	W01	15.92	15.19	104.86
A	W02	18.83	19.70	95.60
B	W02	0.00	19.70	0.00
C	W02	20.56	19.70	104.40
D	W02	20.70	19.70	105.10
E	W02	2.40	19.70	12.18
F	W02	29.36	19.70	149.05
A	W03	30.05	27.03	111.17
B	W03	25.96	27.03	96.03
C	W03	0.00	27.03	0.00
D	W03	23.98	27.03	88.71
E	W03	31.11	27.03	115.09
F	W03	28.10	27.03	103.97

Using filters - wrong wekly median
Employee ID	Week name	Index = calculated column for each employee	Weekly median index	Final employee's index in the week
B	W01	14.45	17.29	83.57
D	W01	18.66	17.29	107.89
E	W01	27.20	17.29	157.32
F	W01	15.92	17.29	92.11
B	W02	0.00	11.55	0.00
D	W02	20.70	11.55	179.26
E	W02	2.40	11.55	20.78
F	W02	29.36	11.55	254.20
B	W03	25.96	27.03	96.03
D	W03	23.98	27.03	88.71
E	W03	31.11	27.03	115.09
F	W03	28.10	27.03	103.97

Using filters - correct weekly median - same as in unfiltered box
Employee ID	Week name	Index = calculated column for each employee	Weekly median index	Final employee's index in the week
B	W01	14.45	15.19	95.14
D	W01	18.66	15.19	122.83
E	W01	27.20	15.19	179.09
F	W01	15.92	15.19	104.86
B	W02	0.00	19.70	0.00
D	W02	20.70	19.70	105.10
E	W02	2.40	19.70	12.18
F	W02	29.36	19.70	149.05
B	W03	25.96	27.03	96.03
D	W03	23.98	27.03	88.71
E	W03	31.11	27.03	115.09
F	W03	28.10	27.03	103.97

I need to get to this 3rd option

Thank you

[KiPa]

@lbendlin - Thanks a lot for your help, this really works well and I learnt new function. You safed me 🙂