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