Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
KevinNC
Frequent Visitor

How to Calculate Population and Rate - Table with Age Groups

 

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

Alabama19991-4 yearsGR113-12710            236,153
Alabama199915-24 yearsGR113-127110            633,971
Alabama199915-24 yearsGR113-12894            633,971
Alabama199915-24 yearsGR113-12916            633,971
Alabama199925-34 yearsGR113-127103            608,323
Alabama199925-34 yearsGR113-12871            608,323
Alabama199925-34 yearsGR113-12932            608,323
Alabama199935-44 yearsGR113-12794            686,932
Alabama199935-44 yearsGR113-12866            686,932
Alabama199935-44 yearsGR113-12928            686,932
Alabama199945-54 yearsGR113-12745            585,651
Alabama199945-54 yearsGR113-12823            585,651
Alabama199945-54 yearsGR113-12922            585,651
Alabama199955-64 yearsGR113-12730            410,542
Alabama199955-64 yearsGR113-12820            410,542
Alabama199955-64 yearsGR113-12910            410,542

 

 

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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 ? 

 

population.PNG

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

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Alabama0.120.120.130.120.130.1
Alaska    0.01 
Arizona0.120.110.140.150.130.13
Arkansas0.050.060.040.060.050.05
California0.60.640.690.780.77

0.76

 

Sum of Total Deaths

State               1999         2000      2001          2002       2003    2004

 
Alabama274278292283311246
Alaska    12 
Arizona275254313338307314
Arkansas10313496129109125
California136814661572180117671792

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.