Combining Data From Unrelated Tables To Create Charts

My take is that these tables could & should be related. You can do this by transforming the data into a dimensional model with conformed Dimension tables. 

If you can invest time into creating a dimensional model, life usually becomes much, much easier. This includes writing DAX, supporting the model, performance tuning the model, applying filters. An example would be someone asking  can you filter this graph to show just   customer Y, for X location, between these dates. With a dimensional model, this is all possible. 

Here is my take on what the model might look like: 

1. Fact Tables:
   • FactInvoices: Contains invoice-related measures and foreign keys to dimensions.
   • FactDeliveries: Contains delivery-related measures and foreign keys to dimensions.
2. Dimension Tables:
   • DimDate: Contains date-related attributes for time-based analysis.
   • DimCustomer: Stores customer information.
   • DimLocation: Holds location data.
   • DimItem: Contains item-related information.
3. Relationships:
   • Both fact tables (FactInvoices and FactDeliveries) have relationships with all dimension tables.
   • The relationships are many-to-one from the fact tables to the dimension tables, represented by the crow's foot notation on the fact table side.

This model allows for flexible analysis across both invoices and deliveries, using shared dimensions. You can also easily extend this model by adding more attributes to the dimension tables or including additional fact tables as needed.

To implement this in Power BI:

1. Create separate tables for each entity in the diagram.
2. Establish relationships between the tables in the Power BI model, matching the relationships shown in the ERD.
3. Create measures in the fact tables for aggregations (e.g., sum of LineTonnage).
4. Use the dimension tables for filtering and grouping in your visualizations.

This structure will allow you to create the area chart showing total tonnage per month, as well as many other types of analyses, while maintaining a clear separation between facts and dimensions.

My favourite quote on this subject comes from the King of DAX - Marco Russo : "

“Honestly, it would be better to be a good data modeler and a mediocre DAX author than a DAX guru but a poor data modeler”