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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.