Forum Discussion
add a calculated value to a visual from another table
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?
4 Replies
- Greg_Deckler
Community Champion
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.
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].
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.
