How to Calculate Population and Rate - Table with Age Groups

[KevinNC]

Hello,

I am trying to correctly calculate population and rate. (See Sample Data Below)  I have done similar calculation with % of Category/SubCategory sales but this is a bit different.

I want to be able to slice and have population and per capita rate calculate correctly when place in measures. The Challenge is the population is the same for each age group/Reason.    So I want I really need is the correct population.  For example the correct population  for  Age Group 15-24 years should be 636971 regardless of what Reason Code(s) selected.  I still need to have the total state population calculated.  Also if states/year multi selected.  The  per capita rate should work if population total is correct. 

Something like:

Per Capita Rate = Divide( [Deaths], [Population], 0)  * 100000

Currently is basically only reports expect values if only ONE group is filtered.

I know the solution is fairly straightforward, but haven't done this before and I can't recall where I have seen an example like this.

[EDIT]

So in other words -- I won't to not doube count population, but do want sum population correctly when any filter(s) are or are not applied.

Thanks, Kevin

Sample Data

State                  Year             Age Group                       Reason               Death               Population

Alabama	1999	1-4 years	GR113-127	10	236,153
Alabama	1999	15-24 years	GR113-127	110	633,971
Alabama	1999	15-24 years	GR113-128	94	633,971
Alabama	1999	15-24 years	GR113-129	16	633,971
Alabama	1999	25-34 years	GR113-127	103	608,323
Alabama	1999	25-34 years	GR113-128	71	608,323
Alabama	1999	25-34 years	GR113-129	32	608,323
Alabama	1999	35-44 years	GR113-127	94	686,932
Alabama	1999	35-44 years	GR113-128	66	686,932
Alabama	1999	35-44 years	GR113-129	28	686,932
Alabama	1999	45-54 years	GR113-127	45	585,651
Alabama	1999	45-54 years	GR113-128	23	585,651
Alabama	1999	45-54 years	GR113-129	22	585,651
Alabama	1999	55-64 years	GR113-127	30	410,542
Alabama	1999	55-64 years	GR113-128	20	410,542
Alabama	1999	55-64 years	GR113-129	10	410,542

[v-piga-msft]

Hi @KevinNC,

I'm a little confused about your scenario, it seems that you want to calculate the population and the rate?

The population in the sample data below is the source data or the measure you created?

For example the correct population  for  Age Group 15-24 years should be 636971 regardless of what Reason Code(s) selected.  

In addition, could you explain the logic why the correct population  for  Age Group 15-24 years should be 636971 ? 

If it it is convenient, could you share expected output, so that we can help further investigate on it?

Best  Regards,

Cherry

[KevinNC]

Hi

Sorry I was confusing.  Thank you for your response,  The reason why the population is repeated for each agegroup and not for reason -- that is how the data is received (from CDC data pull).  Altough, I can use split the data into 2 tables when I pull into Power BI: one with State, Year, AgeGroup, Population, and the second with; reason code, state, year, Agegroup -- I wanted to know how it was possible to do with dax so I could keep the data (fact table) in one table.  Both results should work based on selected/filtered  and/or unfiltered  year(s)/state(s)/Agegroup(s)/reason(s).   Hopefully, that makes sense.

Here is some sample output of what I might want so you can an idea of what I attempting to do:

Sum of Per Capita DeathsYear    

State               1999         2000      2001          2002       2003    2004

Alabama	0.12	0.12	0.13	0.12	0.13	0.1
Alaska					0.01
Arizona	0.12	0.11	0.14	0.15	0.13	0.13
Arkansas	0.05	0.06	0.04	0.06	0.05	0.05
California	0.6	0.64	0.69	0.78	0.77	0.76

Sum of Total Deaths

State               1999         2000      2001          2002       2003    2004

Alabama	274	278	292	283	311	246
Alaska					12
Arizona	275	254	313	338	307	314
Arkansas	103	134	96	129	109	125
California	1368	1466	1572	1801	1767	1792