cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AC1223 Frequent Visitor
Frequent Visitor

How to calculate median by summing values of selected groups

Hi All,
 
Hoping you  could help!!!
 
I have a list of populations for countires, split by age group.
I need to have a slicer on my report showing the age groups.
 
What I need is the median of population - but only summing up the population numbers for the selected age groups.
 
When only 1 age group is selected in the slicer, it is simply the median of the pupulation numbers for that 1 group.
When more that one age-groups are selected in the slicer, i need to sum the population in the selected age groups for each country and then calculate the median.
 
My sample data:
MarketAge GroupPopulation
Country A00-041
Country A05-092
Country A10-143
Country B00-044
Country B05-095
Country B10-146
Country C00-047
Country C05-098
Country C10-149
 
 
eg: in the sample above, if 00-04 and 05-09 age groups are selected, the population for:
 
Country A should be:          1 (00-04) + 2 (05-09) = 3
Country B should be:          4 (00-04) + 5 (05-09) = 9
Country C should be:         7 (00-04) + 8 (05-09) = 15
 
The median in this case should be calculated on the summed values, i.e. 3, 9 and 15, being 9.
 
I am trying to do it using a measure, which will calculate based on what is selected but not having much luck.
I am attaching a sample that has a measure to calculate the median. This works when only 1 age group is selected, but i couldn't figure out how to sum up the values and then calculate the median when multiple age groups are selected.
 
I feel I need to use 'Group By' in my measure somehow, but couldn't work it out.
 
Capture.PNG
 
2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: How to calculate median by summing values of selected groups

Hi @AC1223,

 

Based on my test, the formula below should work in your scenario. Smiley Happy

Measure = 
MEDIANX (
    SUMMARIZE (
        Table1,
        Table1[Market],
        "Total Population", SUM ( Table1[Population] )
    ),
    [Total Population]
)

Just replace Table1 with your real table name.

 

r3.PNG

 

Regards

View solution in original post

AC1223 Frequent Visitor
Frequent Visitor

Re: How to calculate median by summing values of selected groups

Perfect!!!

 

That is exactly what I need.

 

Thankyou @v-ljerr-msft.

Greatly appreciated.

View solution in original post

4 REPLIES 4
Interkoubess Established Member
Established Member

Re: How to calculate median by summing values of selected groups

I created this measure below ( my table is called DataSelect):

 

Measure =
DIVIDE (
    SUM ( DataSelect[Population] ),
    DISTINCTCOUNT ( DataSelect[Market] ),
    BLANK ()
)

You have the output below, please let us know if it is not your expected results.

 

 

Ninter.Data_Select.PNG

AC1223 Frequent Visitor
Frequent Visitor

Re: How to calculate median by summing values of selected groups

Hi Interkoubess,

 

Thankyou for your response.

 

The results are right in this case, but this is not calculating the median.

It does not work with other numbers.

 

eg, if I change the dataset to below: 

 

MarketAge GroupPopulation
Country A00-041
Country A05-09100
Country A10-147
Country B00-0480
Country B05-095
Country B10-142000
Country C00-0430
Country C05-092
Country C10-1450

 

when 00-04 and 05-09 age groups are selected, the population for:
 
Country A should be:          1 (00-04) + 100 (05-09) = 101
Country B should be:          80 (00-04) + 5 (05-09) = 85
Country C should be:         30 (00-04) + 2 (05-09) = 32
 
The median in this case should be calculated as 85, but your formula gives me 72.67
Microsoft v-ljerr-msft
Microsoft

Re: How to calculate median by summing values of selected groups

Hi @AC1223,

 

Based on my test, the formula below should work in your scenario. Smiley Happy

Measure = 
MEDIANX (
    SUMMARIZE (
        Table1,
        Table1[Market],
        "Total Population", SUM ( Table1[Population] )
    ),
    [Total Population]
)

Just replace Table1 with your real table name.

 

r3.PNG

 

Regards

View solution in original post

AC1223 Frequent Visitor
Frequent Visitor

Re: How to calculate median by summing values of selected groups

Perfect!!!

 

That is exactly what I need.

 

Thankyou @v-ljerr-msft.

Greatly appreciated.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors