cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

Dear POWER BI friends,

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...

1 ACCEPTED SOLUTION
Community Champion

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:

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

10 REPLIES 10
Community Champion

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]))

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

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)))

Helper I

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?

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

Dear Paul,

hereby the link to my dataset..

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

Community Champion

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.

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

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 -

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..

Community Champion

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:

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Helper I

thank you Paul!

Helper I

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.  !!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors