use a measure from one table in another

[Anonymous]

For a COVID-19 project, I'm trying to detemine the number of infected people in a postal-code area, based on their age.

Known:

1 table: "Cijfers NL RIVM per leeftijd': infections per age on national scale. "besmet" is Dutch for infected

I've added a calculated column to make the distribution of infected people per age category.

For example, 877 people 80-84 years are infected, which is 9,37% of the total.

2: table 'In het ziekenhuis opgenomen COVID-19 patiënten':  with number of infected people per city. Amsterdam for example has 990 infections.

So when 9,37% of the 990 infected is 80-84 years old, that means 92,7 people of that age are expected to be infected.

And now I want to know how many people per age per postal code are infected.

For that I have the next table:

I know which postal code belongs to which city.

So then I should be able to calculate the number of infected people per postal code and per age category. But how!

In excel it was easy with a lot of extra columns and sumifs, but can it be done in Power BI. Sorry for the open question, hope someone can help.

update: the solution was found with a number of postst below here. 

[bheepatel · Accepted solution]

Hi @Anonymous 

I assume in your last table (Table 3) you have the following columns: Age Group, Postal Code & Population and you want to have another column that can tell you the number of people infected?

What you can do is create a Fourth Column in Table 3 and use the LOOKUPVALUE() function, where you can look up the % from Table 1 by matching the Age Group from Table 3 to Table 1 as follows:
Fourth Column = LOOKUPVALUE('Table1'[besmet leeftijd tov totaal], 'Table1'[Leeftijdgroep], 'Table3'[Leeftijd])

This will give you a % infection rate for each row in your Table 3. You can then create a Fifth Column to give you number of people infected:

Fifth Column = Fourth Column * Bevolking(aantal)

Hope this helps!

[Anonymous]

Yes, I'm a bit futher now, that lookupvalue helps.

I've added some columns

[percentage leeftijd tot totaal] = what you suggested. But then I need to know how many infected people of that age group are in the city, how may people of that age group actually live in that city, and then I can calculate the number of infected people.

added column [gemeente] is city matching the postal code

added column [besmet per stad] is the number of infected people per city

added column:  besmet per age per gemeente = 'aantal per postcode'[percentage leeftijd tov totaal] *'aantal per postcode'[besmet per stad]

But now I need to know how many people per age group live in a city. In Excel I would use SUMIFS and filter on age per row and city per row. So the top row show the total number of "bevolking" for age 90-94 for Amsterdam. How do I do this? 

[bheepatel]

You can create a new table that has a distinct combination of City & Age Group as follows:

New Table = SUMMARIZE('aantal per postcode', 'aantal per postcode'[gemeente], 'aantal per postcode'[leeftijd]).

In this New Table, you can create two new columns that will give the population per age group per city and the number of people infected per age group per city. You can use the following DAX functions to determine the two columns:

New Column = CALCULATE ( SUM( ), FILTER( ) )

The CALCULATE, SUM and FILTER functions will serve the same purpose as SUMIFS and filter in Excel.

[Anonymous]

Thanks for the help so far!

It's working partly.

1. The table went fine!: 

New Table = SUMMARIZE('aantal per postcode';'aantal per postcode'[gemeente];'aantal per postcode'[leeftijd])

2. first calculated column also.

population per age group per city =

     CALCULATE(

         SUM('aantal per postcode'[Bevolking (aantal)]);

               FILTER ('aantal per postcode';'aantal per postcode'[gemeente]='New Table'[gemeente]);

               FILTER ('aantal per postcode';'aantal per postcode'[Leeftijd] = 'New Table'[Leeftijd]))

3. But the second one seems only to filter on the city and not the age group.

number of people infected per age group per city =

      CALCULATE(

            sum('In het ziekenhuis opgenomen COVID-19 patiënten'[Besmet]);

                   filter('aantal per postcode';'aantal per postcode'[Leeftijd]='New Table'[Leeftijd]);

                   filter('aantal per postcode';'aantal per postcode'[gemeente] = 'New Table'[gemeente]))

Almost there, what am I doing wrong here?

[bheepatel]

Hi @Anonymous 

I had a relook at the data. In Table1, you are given the infections per age on national scale. From there, you calculate a percentage which is the % infections in one age group of total infections. It is not % infections in one age group of total population in that age group.

If we take your initial example, of age group 80-84: there are 2035 infected people nationally which is 9.37% of all infected people nationally. It is not 9.37% of the national 80-84 age group population. 

As a result, I do not think you can multiply the population of a city per age group to the percentages that you have calculated per age group. Because the percentages are a ratio of infected people per age group to the total number of infected people.

Hope that makes sense?

[Anonymous]

Thanks for the thorough look. It does make sense.

Given is for example 900 infected people in Amsterdam. But as the age piramid is different from the nation, how do we know how those infected people can be divided per age category?

From the national view, 9,37% of infected people is 80-84. This means that 9,37% of 900 = 84,33 infected people are in that age category.

In Amsterdam are 2535 people of 80-84years of age.

In postal code-area 1059 (in Amsterdam), there are 1059 people of 80-84y. That is 1059/2535 = 41,7%

That means that in that postal code 41,7% of 84,33 = 35,22 infected people of 80-84 are to be expected.

The last comment you gave but deleted did do the trick. Only I had to use MAX instead of SUM 

number of people infected per age group per city =

CALCULATE(

MAX('aantal per postcode'[besmet per age per gemeente]);

filter('aantal per postcode';'aantal per postcode'[Leeftijd] = 'New Table'[Leeftijd]);

filter('aantal per postcode';'aantal per postcode'[gemeente] = 'New Table'[gemeente]))

I will finish this up when I have time in the following days. Thanks a lot so far!

[bheepatel]

That makes sense - thanks for the example. I guess the assumption there is that the split of infected people in the different postal codes within a city is proportional to the population density 🙂 

You are right - SUM would not have been correct. Either AVERAGE or MAX would have worked.

Glad it worked out!