cancel
Showing results for
Did you mean:
Helper IV

How to calculate median by summing values of multiple groups

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
5 REPLIES 5
Community Support

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.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User IV

@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``````

Proud to be a Super User!

Helper IV

Super User IV

@Sriku

Proud to be a Super User!

Super User IV

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

Proud to be a Super User!

Announcements