Percentage calculation... can't find the right DAX formula ....

[Katja262373]

Dear POWER BI friends,

could you please advice, how to calculate a percentage?

I have 2 tables (please see the attached printscreens): in the first table there are the given amounts of immigrants in EU countries in 2013-2022 (per land), and in the second table - total population of EU countries in 2013-2022 (per land).

How can I calculate the percentage of immigrants per land based on total amount of population per land?

My DAX-formula doesn’t work, unfortunately…

Percentage 2013= DIVIDE (‘immigrants in EU’[2013], ‘total population EU country’[2013])

Percentage 2014= DIVIDE (‘immigrants in EU’[2014], ‘total population EU country’[2014])

Etc.

hereby the 2nd table - total population EU country

population of EU countries (per country 2013-2022)

and the first table - immigrants in EU

immigrants in EU countries 2013-2022

below is an example of a graph, which I'd like to create...

thank you in advance for your kind advice!

[PaulDBrown]

First of all you need to unpivot the year columns in each table in Power Query:

Select the geo column, right click and select "unpivot other columns".

Then it's a simple measure:

% of inmigrants over population = DIVIDE(SUM(immigrants in EU[Value]), SUM(total population EU country[Value]))

[Katja262373]

Maybe this formula (with Filter) can be a solution?

%2013 = DIVIDE(SUM('immigrants in EU'[Value]), FILTER('immigrants in EU','immigrants in EU'[Value] = 2013), SUM('total population EU'[Value]), FILTER('total population EU','total population EU'[Value] = 2013)))

[Katja262373]

Hi dear Paul, thank you a lot for your kind response and advice!! I've tried to apply this solution and that's the result, but I need to know the annual change of the percentage per country... I'm sorry, I guess I didn't express my goal clearly.. 

should I then add years (instead of [Value] in my DAX formula?

 

[PaulDBrown]

Can you provide sample data and a depiction of what you want to show?

[Katja262373]

Dear Paul,

hereby the link to my dataset..

https://drive.google.com/file/d/1TZWY9d6PaO9GJ2GjahvVEQXonOC7sCqa/view?usp=drivesdk

and the expected graph should be a linear diagram, just like the one in my first post

[PaulDBrown]

Sorry, I acannot access the sample. It requires permission. Can you provide an open link?

In the meantime, if what you need is a similar graph to the one you have shown, you could try a line graph: add the year field (column in the table) to the x-axis, the [% of inmigrants over population] measure to the values bucket (y-axis), and the geo field to the legend.

[Katja262373]

Dear Paul, I've updated the link, hope that you can now open it !! 
if it still doesn't work, you can access the data set via Eurostat site -

https://ec.europa.eu/eurostat/databrowser/view/migr_pop2ctz__custom_9045571/default/table?lang=en

here you should click plus (+) by "Country of citizenship" and then tick in the boxes "Foreign country and stateless" and "Reporting country".. please see the attached print screens..

 

 

[PaulDBrown · Accepted solution]

So you need to set up the data in tables as follows:

Create dimension tables for geo and year, and set up the model as follows

 Create the measures:

Population = SUM(Population[Population])

Immigration numbers = SUM('Immigration Numbers'[Immigrants])

% Immigration over population = DIVIDE([Immigration numbers], [Population])

and finally set up a line chart as follows:

Here is the link to the PBIX file:

https://1drv.ms/u/s!AhGKDZz6e4HehKkAZKs28vtim16EHA?e=cHmn36 

[Katja262373]

thank you Paul!

[Katja262373]

Oh my God, this is awesome! thank you very much for your help, Paul! I didn't expect, that I'd need to create links between tables in a Model window.. couldn't take my head around it...

Thank you a lot, this is a revelation.  !!