cancel
Showing results for
Did you mean:
Highlighted
AC1223 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:
 Market Age Group Population Country A 00-04 1 Country A 05-09 2 Country A 10-14 3 Country B 00-04 4 Country B 05-09 5 Country B 10-14 6 Country C 00-04 7 Country C 05-09 8 Country C 10-14 9

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. 2 ACCEPTED SOLUTIONS

Accepted Solutions 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. ```Measure =
MEDIANX (
SUMMARIZE (
Table1,
Table1[Market],
"Total Population", SUM ( Table1[Population] )
),
[Total Population]
)
```

Just replace Table1 with your real table name. Regards

AC1223 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.

4 REPLIES 4
Interkoubess 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. AC1223 Frequent Visitor

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

Hi Interkoubess,

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:

 Market Age Group Population Country A 00-04 1 Country A 05-09 100 Country A 10-14 7 Country B 00-04 80 Country B 05-09 5 Country B 10-14 2000 Country C 00-04 30 Country C 05-09 2 Country C 10-14 50

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 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. ```Measure =
MEDIANX (
SUMMARIZE (
Table1,
Table1[Market],
"Total Population", SUM ( Table1[Population] )
),
[Total Population]
)
```

Just replace Table1 with your real table name. Regards

AC1223 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.

Announcements #### 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. #### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January! Top Solution Authors
Top Kudoed Authors
Users online (3,412)