Make the % of the value in comparison to the Total of that year

[Anonymous]

Hello! 

I'm pretty new with PowerBi and found realtively easily answers to my different questions. But now I'm stuck.

I have a whole list of different countries summarizing the value of exported goods and services depending of the year.

For example:

Germany / 2013 / 67385 M euros

Bahamas / 2014 / 25 M euros

And so on ...

I would like to create a new column which gives the percentage in comparison to the total of that year

For example; if 2014 had 100 M euros in total of exported goods and services, Bahamas would represent 25%.

But I can't seem to find a formula that can make a division (Value / SUM(Values)) depending on the year. It usually takes all the values, whichever the year is..

Thanks in advance!

[mussaenda]

Hi,

On the values, we can select this one so it can display the GT %

[Anonymous]

Hey mussaenda! Thanks for your quick answer, but the thing is, it only calculates the % based on the filtered values of the dashboard.

If I select 5 countries, it will show a percentage based on these countries. I want it to be based on all the years, whichever country I select in the dashboard.

[mussaenda]

% of Grand Total =
DIVIDE (
SUM ( Value Biens (M Euro) ),
CALCULATE ( SUM ( Value Biens (M Euro) ), ALL ( 'Table1'[TIME]  ) )
)

I see, I get you.

You may want to try this one.

[Anonymous]

Thanks! Tried it and I get various numbers which are too high to be percentages.

Can I have some explanation about the formula? I'm kinda lost  

[mussaenda]

You may want to check this thread.

Sorry, instead of TIME column, please use the Partner Name column.

This is what you need, right?

if not, please provide more info for testing.

Thank you

[Anonymous]

The table you provided is exactly what I'm looking for. But the formula doesn't give the same result..

This is what I have: 

This is with PartnerName

This is with TIME

[mussaenda]

Sorry @Anonymous, it works with a measure.

[Anonymous · Accepted solution]

Thanks for your help! Even when I did a measure, it didn't want to apply it to the whole dataset, only to the selected values. So I made a workaround.

I created anothe table whick makes a group by (sum) of the values of each year. Afterwards I divide the value by the total number of that year (by using a lookupvalue).