Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Katja262373
Helper I
Helper I

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

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)population of EU countries (per country 2013-2022)

 

and the first table - immigrants in EU

immigrants in EU countries 2013-2022immigrants in EU countries 2013-2022

 

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

example (1).jpg

thank you in advance for your kind advice!

1 ACCEPTED SOLUTION

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

table set up.png

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

model setup.png

 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:

line chart.png

 

Here is the link to the PBIX file:

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

10 REPLIES 10
PaulDBrown
Community Champion
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]))





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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?

IMG_0739.jpeg

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

IMG_0768.jpeg

IMG_0767.jpeg

 

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

table set up.png

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

model setup.png

 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:

line chart.png

 

Here is the link to the PBIX file:

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

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






thank you Paul!

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors