Hi, I am new to powerpivot and battling to come to grip with relationships in DAX measures. I am currently working on a problem that I am sure is easily fixed with the correct DAX formula.
I have 5 tables: FinancialData, Shipment, VA, Container & Voyage. I am trying to create a pivot from these tables that shows:
There are two types of jobs that and each have their own table i.e. Prefix V jobs are in the VA table as well as the Financial Data table while Prefix J jobs are in the Shipment table and the Financial Data table. Container numbers and weight are related to the Shipment table jobs only (prefix J jobs).
While I can get a pivot to show amounts by vessel & voyage and another pivot to show containers and weight by vessel & voyage, I can’t show all in a pivot table or use amount and weight in the same measure. Can anyone help.
I have 5 tables: FinancialData, Shipment, VA, Container & Voyage. I am trying to create a pivot from these tables that shows:
Voyage | Vessel | Amount | Containers | Weight(T) | Amount/Ton |
1N | Fred | 1000 | 16 | 80 | 12.50 |
1S | Fred | 1100 | 20 | 120 | 9.17 |
2N | Joe | 600 | 9 | 40 | 15.00 |
2S | Joe | 600 | 36 | 80 | 7.50 |
3N | Mary | 200 | 13 | 60 | 3.33 |
3S | Mary | 200 | 24 | 60 | 3.33 |
4N | Paul | 200 | 18 | 60 | 3.33 |
4S | Paul | 300 | 8 | 20 | 15.00 |
There are two types of jobs that and each have their own table i.e. Prefix V jobs are in the VA table as well as the Financial Data table while Prefix J jobs are in the Shipment table and the Financial Data table. Container numbers and weight are related to the Shipment table jobs only (prefix J jobs).
While I can get a pivot to show amounts by vessel & voyage and another pivot to show containers and weight by vessel & voyage, I can’t show all in a pivot table or use amount and weight in the same measure. Can anyone help.