Many-to-many relationships on World Bank data

[Nexans]

Hi. 

I'm just starting to learn Power BI and is struggeling to gasp some basic consepts. I have connected to various data sources from world bank through their API, and is now trying to create some calculated colums. 

My first task was to calculate GDP per capita by dividing GDP with population from the population table. Problem I am facing is that this is not possible. I think my problem is the data model, but I am not able to figure this one out.

Can somebody please assist? See the attached file.

Download Power BI file

[v-juanli-msft · Accepted solution]

Hi @Nexans

Steps as below:

1.Create calculated columns in the "GDP, Current USD" table

population =
CALCULATE (
    SUM ( 'Population, total'[Population] ),
    FILTER (
        ALL ( 'Population, total' ),
        'Population, total'[CountryID] = EARLIER ( 'GDP, Current USD'[CountryID] )
            && 'Population, total'[Year] = EARLIER ( 'GDP, Current USD'[Year] )
    )
)

2.Create measure in the same table

GDP = SUM('GDP, Current USD'[GDP USD ($)])
DIVIDE = DIVIDE([GDP],MAX('GDP, Current USD'[population]),0)

In the table, there is a row with blank row in “country” column, but with values in “DIVIDE” measure,

This is because there are some “country id“ exist in the” GDP, Current USD” and “Population, total” table, but not exist in the “Countries” table.

Best Regards

Maggie

[v-juanli-msft]

Hi @Nexans

Since your pbix contains some real data, I would send you a private message including the modified pbix.

Please check if you receive it.

Best Regards

Maggie