Calculating the same value in 2 different columns

[LACEG]

Hi All,

I am new to Power BI and think it is a great analysis tool.  I am attempting to create a report but I am facing issues (due to being new to the tool).

The intention of the report is to show the number of imports (column 1) and the number of exports (column 2) from a specific shipping port (location) for a given customer (row 1).

This mean that the port needs to be selected before the report is generated.

Once the port has been chosen, then this value needs to be checked against every record in the customer table looking at the 'import' and 'export' columns.  If the chosen port matches either the import or export values then this should be counted else it should not be counted. 

Here is an example:

This is how the underlying data looks:

Consignee	Import	Export
Customer 1	LIS	NYC
Customer 2	NYC	LEI
Customer 3	LEI	NYC
Customer 4	NYC	LIS
Customer 5	LEI	NYC
Customer 6	NYC	LIS
Customer 7	LEI	NYC
Customer 8	LEI	NYC
Customer 9	LEI	NYC
Customer 10	NYC	LIS

So if the 'Port' was selected as 'LIS' then the following values should be returned:

Port	LIS
Customer	Imports	Exports	Total
Customer 1	1		1
Customer 4		1	1
Customer 6		1	1
Customer 10		1	1
Total	1	3	4

Can anyone give me some pointers on how I can calculate the chosen port against the I'mport' and 'Export' columns in the underlying data table?

Thank you in advance.

[LACEG]

Can anyone help me with my query? 🙂

[greggyb · Accepted solution]

1. It's been five hours, calm down.
2. Here's a sample .pbix for you.

You need to unpivot your import and export columns (this is done in Edit Queries). This allows a much cleaner measure setup:

// DAX
// Measures

Total =
COUNTROWS( FactShipment )

Imports =
CALCULATE(
    [Total]
    ,FactShipment[ShipmentType] = "Import"
)

Exports =
CALCULATE(
    [Total]
    ,FactShipment[ShipmentType] = "Export"
)

[LACEG]

Thank you, this solution you have provided has done it

[LACEG]

Thank you greggyb - I will implement this now.

I apologise for my eagerness.