cancel
Showing results 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

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

 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

2 REPLIES 2
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 ?

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.
Frequent Visitor

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

Announcements

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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors