Finding mutual values for two columns visualized in a table

[rvankralingen]

Dear,

I just started with PowerBI after an exstensive period making use of MS Excel.

I am currently making a dashboard from logistic perspective for my company. 

My aim is to create a visual which shows mutual destinations and origins per carrier for respectively export and import incl. count of shipments.

It must look like below.

Example:

If value in Column Destination and Column Origin is equal then this mutual value must be displayed as in below table incl. count of shipment numbers for Destination (Export) and Origin (Import). 

Carrier X

Shared destination/origin	Export	Import
USA (mutual destination & origin)	100	50
Mexico (mutual destination & origin)	40	10
France (mutual destination & origin)	20	5

How do I need to arrange/model my data in order to achieve this? (helping columns, formulas)

Thank you in advance for the responses.

KR Robert

[Ashish_Mathur]

Hi,

Not clear with your question.  Share the input and the output table.

[rvankralingen]

I have simulated this situation in MS Excel making use of Input and Ouput table below. 

Input table: is a pivot table

Output table: normal table

In Excel I created a formula which seeks mutual countries with following formula:
=IFERROR(UNIQUE(FILTER([ImportColumnTable1];ISNUMBER(XLOOKUP([ImportColumnTable1];ExportColumnTable1];0))));"")

This formula results in table below:

Mutual

Mexico

India

Malaysia

With a vlookup I search the corresponding count of shipments from Pivot table which results in Output table.

Input table (Pivot table)

Import	Count of shipments	Export	Count of shipments
China	27	United states	8
Turkey	13	Mexico	6
Mexico	4	Venezuela	3
India	3	Congo (Kinshasa)	1
South Korea	3	Oman	1
Malaysia	2	India	1
Morocco	2	Canada	1
Chile	1	Malaysia	1
Thailand	1

Output table (normal table)

Mutual	IM	EX
Mexico	4	6
India	3	1
Malaysia	2	1

[Ashish_Mathur]

Hi,

So you wish to create a 3 column table with only those countries showing which are present in the Import and Export columns.  If my understaning is correct, then share the actual data (not the Pivot Table).

[v-jingzhang]

Hi @rvankralingen 

Can you share some sample data in table format to show what you have in the original table? How do you count the numbers for Destination (Export) and Origin (Import) based on that table? I guess probably there are Destination and Origin columns in the table, but I don't know how to get the count you want with only these two columns. There must be some other columns and logics to help the calculation.  

Best Regards,
Community Support Team _ Jing