Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Combine information loaded from two separate cvs-files

I have loaded two data sources in csv-format:

One is a table which has all the countries of Europe and their population and the Covid-19 cases/ deaths for every day from March to July, 3rd, respectively.

 

DateCovid-19 casesDeaths caused by Covid-19CountryPopulation 2020
Mar 1, 2022some numbersome numberAustraliasome number
Mar 2, 2022some numbersome numberAustraliasome number
...............
Jul 3, 2022some numbersome numberAustralia

some number

Mar 3, 2022some numbersome numberBelgium

some number

 

Another one is a table with all the countries of  Europe and their area in km^2 in 2020, i.e., the table looks like

 

CountryArea in 2020 (in km^2)
Australiasome number
Belgiumsome number
......
......

 

Now, I want to plot at least two diagrams:

 

(1) A diagram that shows which countries are "most relevant" for Covid-19 from March to July 3rd, 2022.

(2) A diagram which shows which shows the most relevant countries with respect to Covid-19, but with dependence on the population density (i.e. number population divided by area).

 

For (1) I am not sure how to plot since I have so many data.

 

For (2) I first need to compute the population density but I do not know how I can do so, because the first table contains the population of 2020 but the second table contains the area of 2020.

 

I need some help, please.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample. The two tables are related with the Country column.

    Covid-19 Table:

    Area Table:

    In my sample, Covid-19 cases and Deaths caused by Covid-19 are all cumulative totals each day. Here's my solution.

    Create three measures.

    Case Relevant =
DIVIDE (
    AVERAGE ( 'Covid-19'[Covid-19 cases] ),
    MAX ( 'Covid-19'[Population 2020] )
)

    Death Relevant =
DIVIDE (
    MAX ( 'Covid-19'[Deaths caused by Covid-19] ),
    MAX ( 'Covid-19'[Population 2020] )
)

    Population density =
DIVIDE ( MAX ( 'Covid-19'[Population 2020] ), MAX ( 'Area'[Area] ) )

    I choose the average value of case number and max value of death number for calculation, you can also choose sum etc.

    For the first diagram, we can put country and two two measures in a table visual. For the second  diagram, we can choose a scatter chart. From the scatter chart , can see the population and case number are positive correlation.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    If the second table has a single line for each country then you can make a relationship between both table by country and then use the second table has a dimension to use to make the calculations.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample. The two tables are related with the Country column.

    Covid-19 Table:

    Area Table:

    In my sample, Covid-19 cases and Deaths caused by Covid-19 are all cumulative totals each day. Here's my solution.

    Create three measures.

    Case Relevant =
DIVIDE (
    AVERAGE ( 'Covid-19'[Covid-19 cases] ),
    MAX ( 'Covid-19'[Population 2020] )
)

    Death Relevant =
DIVIDE (
    MAX ( 'Covid-19'[Deaths caused by Covid-19] ),
    MAX ( 'Covid-19'[Population 2020] )
)

    Population density =
DIVIDE ( MAX ( 'Covid-19'[Population 2020] ), MAX ( 'Area'[Area] ) )

    I choose the average value of case number and max value of death number for calculation, you can also choose sum etc.

    For the first diagram, we can put country and two two measures in a table visual. For the second  diagram, we can choose a scatter chart. From the scatter chart , can see the population and case number are positive correlation.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.