Forum Discussion
Calculating the same value in 2 different columns
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.
- It's been five hours, calm down.
- 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" )
4 Replies
Can anyone help me with my query? :)
- It's been five hours, calm down.
- 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" )Thank you greggyb - I will implement this now.
I apologise for my eagerness.
