I would please like some advice or other suggestions as to what would be the best solution for this problem. I've posted in StackOverflow as well: https://stackoverflow.com/questions/66243432/power-pivot-create-from-existing-table-unique-id-table-...
I have 2 different data files that Power Query pulls in and transforms (One stock file, other one Sales). These are both loaded into the Data model and measures added, and then used in various Pivot Table Reports. Both these Data tables have 100 000's of rows and between 30 and 50 columns.
One of the Pivot Table reports I need to combine these to Data files to show stock and Sales. This cannot currently happen because it's a many to many relationship. I'm adding all the Pivot Table columns (Site, Article, Barcode, StockQty, etc) from the Stock file and just want to add the Sales Value and Units from the Sales File. Columns that need to match between the Stock and Sales data are Site and Barcode.
I've tried:
- Just adding the sum measure from the sales file to my Pivot Table report
Total Sales:=CALCULATE(SUM(DailySalesRaw[SalesValue]),DailySalesRaw[SalesQuantity]>0)
But then all the values are the same because of the many to many relationship.
- Adding a measure to my Stock file (Unit_Packsize table are 'Master tables' with distinct Barcodes, but they do not include all the Barcodes found on the data files). This seem to correctly add the values, but I want to avoid using this table since it's not always up to date and will exclude items not found (Pack size=N/A), thus not all values will be added.
Sales Total:=CALCULATE(SUM(DailySalesRaw[SalesValue]),DailyStockData,UnitSize_PackSize)
- Tried aggregating the 2 data files in Power Query to create a single table that has 2 columns with unique ID's(Site and Barcode). I can then create one to many relationships, which should solve the problem. I believe there should be an easier way....
Question:
What is the best way to do this:
- Use Power Query to create a lookup ID table from the Sales data? (This report is run every week, so constantly uses new data files)
- Is there a formula in Power Pivot Data Model that can sum the total value when the Site and Barcode match?
- How to create a lookup ID table that only includes distinct Site and Barcode from the Sales table in Power Pivot Model? (Without manually creating a table as mentioned above. I've only managed to use distinct with 1 column)
Image of Data Model layout:
