cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sriku
Helper IV
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)
AS2021015.590=MEDIAN(IF($A$2:$A$20=A2,IF($D$2:$D$20=0,$C$2:$C$20)))
Ga2021012.9202.917647
Ib2021017.6407.641509
IC2021017.2307.232143
No2021014.0003.191667
UK2021016.6206.619048
Af2021012.2502.25
AN2021015.7205.722222
Gr2021011.771#NUM!
In2021017.3307.325758
Ja2021015.3105.313725
La2021014.4504.454198
ME2021014.111#NUM!
SE2021015.8005.803279
Ca2021016.0006
Mi2021012.2802.283333
No2021012.3803.191667
So2021013.2103.210145
We2021012.2302.229508
5 REPLIES 5
v-shex-msft
Community Support
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.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Fowmy
Super User IV
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
    

 

 

 

Fowmy_0-1620129210282.png

 

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



 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Can you please share your working.

Fowmy
Super User IV
Super User IV

@Sriku 

Please check below my signature.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors