calculate median based on team

[hpatel24779]

Hi,

I have a table consisting of Person ID, Team, Wait_weeks.

Person ID	Team	Wait_weeks
1	A	1
2	A	2
3	A	2
4	A	3
5	B	1
6	B	2
7	C	4
8	C	1
9	C	9
10	D	1
11	D	3
12	D	5
13	D	8
14	D	4
15	E	5
16	E	2
17	E	6
18	E	9

i want to create a measure that calculates the median wait_weeks if the team matches criteria.

for example, i want to find the median where team is A or D, median if team is C or E. Then i want to create a summary table as below:

Team	Median by Team	Median by Group
A	2	3
B	1.5	1.5
C	4	5
D	4	3
E	5.5	5

I can get median by team but need help with the dax measure for median by group.

can anyone help me.

kind regards

Hetal

[hpatel24779]

Thank you @Kedar_Pande  and @SamWiseOwl. tested your solutions and does exactly what i needed it to do.

much appreciated

[Kedar_Pande]

Measure

Median_by_Team = 
MEDIANX(
VALUES('YourTable'[Person ID]), 
'YourTable'[Wait_weeks]
)

Measure for Median by Group:

Median_by_Group = 
SWITCH(
TRUE(),
'YourTable'[Team] IN {"A", "D"}, 
MEDIANX(
FILTER(
'YourTable',
'YourTable'[Team] IN {"A", "D"}
),
'YourTable'[Wait_weeks]
),
'YourTable'[Team] IN {"C", "E"},
MEDIANX(
FILTER(
'YourTable',
'YourTable'[Team] IN {"C", "E"}
),
'YourTable'[Wait_weeks]
),
BLANK() // Default case if no criteria match
)

Add a Table Visual in Power BI.
Include the Team column from your original table.
Add the measures Median_by_Team and Median_by_Group to the values area of the table.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[SamWiseOwl · Accepted solution]

Hi @hpatel24779 
The code will look something like this:

Median by group =

SWITCH(

    TRUE()

    ,SELECTEDVALUE('median table'[Team]) IN {"A","D"}, CALCULATE(MEDIAN('median table'[Wait_weeks]), 'median table'[Team] IN {"A","D"})

    ,SELECTEDVALUE('median table'[Team]) IN {"C","E"}, CALCULATE(MEDIAN('median table'[Wait_weeks]), 'median table'[Team] IN {"C","E"})

    ,MEDIAN('median table'[Wait_weeks])

)

For B I had it return just B's Median: