How to calculate median by summing values of selected groups

[AC1223]

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.

[v-ljerr-msft · Accepted solution]

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

[Interkoubess]

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]

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: 

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 · Accepted solution]

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 · Accepted solution]

Perfect!!!

That is exactly what I need.

Thankyou @v-ljerr-msft.

Greatly appreciated.