Forum Discussion
Finding mutual values for two columns visualized in a table
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
4 Replies
v-jingzhangCommunity Support
Hi Anonymous
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- Ashish_Mathur
Super User
Hi,
Not clear with your question. Share the input and the output table.
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).
