How to calculate median by summing values of multiple groups

[Sriku]

Hi,

How to calculate Median in filtering 2 groups Category and Pandemic:

Caterory(cell-A)	Month(cell-B)	HTR(cell-C)	Pandemic(cell-D)	Median(cell-E)
AS	202101	5.59	0	=MEDIAN(IF($A$2:$A$20=A2,IF($D$2:$D$20=0,$C$2:$C$20)))
Ga	202101	2.92	0	2.917647
Ib	202101	7.64	0	7.641509
IC	202101	7.23	0	7.232143
No	202101	4.00	0	3.191667
UK	202101	6.62	0	6.619048
Af	202101	2.25	0	2.25
AN	202101	5.72	0	5.722222
Gr	202101	1.77	1	#NUM!
In	202101	7.33	0	7.325758
Ja	202101	5.31	0	5.313725
La	202101	4.45	0	4.454198
ME	202101	4.11	1	#NUM!
SE	202101	5.80	0	5.803279
Ca	202101	6.00	0	6
Mi	202101	2.28	0	2.283333
No	202101	2.38	0	3.191667
So	202101	3.21	0	3.210145
We	202101	2.23	0	2.229508

[Anonymous]

Hi @Sriku,

Did these suggestions help with your scenario? if that is the case, you can consider Kudo or accept suitable suggestions to help others who faced similar requirements to find it more quickly.

If these also not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Fowmy]

@Sriku 

Create the following measure and add it to the table visual

Median Measure = 

var __Cat = SELECTEDVALUE(Table2[Caterory(cell-A)])
var __median = 
    CALCULATE( 
        MEDIAN(Table2[HTR(cell-C)]),
        Table2[Caterory(cell-A)] = __Cat,
        Table2[Pandemic(cell-D)] = 0,
        ALL(Table2)
        
    )
return
  __median
    

If you need to add it as a column to your table then, use the following code:

Median Column = 

var __Cat = Table2[Caterory(cell-A)]
var __median = 
    CALCULATE( 
        MEDIAN(Table2[HTR(cell-C)]),
        Table2[Caterory(cell-A)] = __Cat,
        Table2[Pandemic(cell-D)] = 0,
        ALL(Table2)
        
    )
return
  __median

[Sriku]

Can you please share your working.

[Fowmy · Accepted solution]

@Sriku 

Please check below my signature.

[amitchandak]

@Sriku , Can you explain the formula. Not a expert on excel