Creating a bar chart with special condition

[FabiNeed]

I face the following challenge and am grateful for any help:

I have two different tables from which I want to create a bar chart. In Table 1 there is a Unique ID in the first column and country codes in the second and third column. The countries can be identical, but also different for the same Unique ID:

UniqueID		Country_1	Country_2
1444765|5439351		AFG	AIA
2329290|5683159		ALB	AFG
1874843|6096713		DZA	ALB
2149806|5298293		ASM	DZA
3248770|6395273		AND	ASM
3507469|4342633		AGO	ALB
3038413|4648776		AIA	DZA
2809583|4398770		ATA	ASM
2868538|5369172		ATG	AND
2146966|5620333		ARG	AND

In Table 2, the first column contains a (fancy) category name, followed by several columns, each containing values for a specific country per category:

Category	AFG	ALB	DZA	ASM	AND	AGO	AIA	ATA	ATG	ARG
Famone	1	3	2	2	1	2	1	1	1	1
Omicet	1	1	2	3	3	2	1	2	1	1
Pausne	1	2	2	3	3	1	1	3	1	3
Bizoad	1	2	3	2	1	2	2	2	3	2
Peamul	2	1	2	3	3	2	1	3	2	3
Aconat	3	3	2	3	3	2	3	1	1	2

The objective is, to ...

• ... check for each Unique ID which country has the higher value in each category (e.g. for "1444765|5439351" and the category "Famone" AIA would has the higher value (2) compared to AFG (1).
• ... create a bar chart that looks like this (with the correct values, of course):
•  

I uploaded a PowerBI file with the raw data from the tables: PowerBI file

I am happy for any ideas and solutions.

[v-yanjiang-msft]

Hi @FabiNeed ,

According to your description, I download your pbix. As shown below, each ID corresponds to unique country.

So you can get "which country has the higher value in each category" or "which Unique ID has the higher value in each category", they are actually the same result. But I'm not sure if that's what you mean by "for each Unique ID which country has the higher value in each category".

If that is the case, put category in the bar chart Axis, country in the Legend and value in Values, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FabiNeed]

Thank you @v-yanjiang-msft for this suggestion. Unfortunately, this is not quite what I was looking for. I'll try to make it a little clearer :

Let's take the first row from Table 1:

UniqueID		Country_1	Country_2
1444765|5439351		AFG	AIA

There is a UniqueID with two different countries. For each country, there is a value in Table 2. In this case: 

Category	AFG	AIA
Famone	1	1
Omicet	1	1
Pausne	1	1
Bizoad	1	2
Peamul	2	1
Aconat	3	3

This reads, for example: For the Category "Bizoad" the value for Country "AFG" is 1 and for Country "AIA" 2. My objective is to automatically choose the higher value for the bar chart visualization. In the visualisation the values (1 to 3) should be taken as the legend. Do you (or of course anyone else) have a solution for this as well?