add a calculated value to a visual from another table

[im_deacha]

I have a table with the number of students, which has filters for year, city, type of service, type of funding, etc.
I have another table with the population by city for every year.

i have a visual table in which i want to displaythe sum of the students by year (filtered by slicers), and the population by year (filtered only by city slicer). how can this be done?

example:

students

Year   |City |service |funding |students
2019  |1     |1          |1            |10
2019  |2     |2          |1            |20
2019  |3     |1          |2            |30
2019  |1     |2          |2            |40
2018  |2     |1          |1            |15
2018  |3     |2          |2            |20
2017  |3     |2          |1            |10

population

year  |city |population
2019 |1     |100
2019 |2     |120
2019 |3     |140
2018 |1     |110
2018 |2     |125
2018 |3     |150
2017 |1     |115
2017 |2     |135
2017 |3     |155

visual: (all slicers set on all)
year   |students  |population
2019  |100          |360
2018  |35            |385
2017  |10            |405

chaning the slicer value of service and funding should only change the students, but the slicer for city should change both population and students

does anyone know a way to do this?

[FrankAT · Accepted solution]

Hi @im_deacha 

take a look at the following solution. Attached pbix file at the bottom:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[im_deacha]

thnx, this worked.

I had tried this but was adding the year from the students table instead from the year table, since i had already that one on the visual table. I didn't expect that to be an issue.

[Greg_Deckler]

@im_deacha Are the tables related or not related to one another? If related, just put the related column in the visual. If unrelated, you will need to create a column or measure using LOOKUPVALUE, probably measure.

[im_deacha]

i tried adding a relationship using year-city as an id in population and adding it in students. but it duplicates the population in cases in which the same city has more then 1 service or funding for the same year.

i tried lookupvalue but couldnt get it to work, it wont accept the search value as students[year].