Feb 18 2021 02:57 AM
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:
Total Sales:=CALCULATE(SUM(DailySalesRaw[SalesValue]),DailySalesRaw[SalesQuantity]>0)
But then all the values are the same because of the many to many relationship.
Sales Total:=CALCULATE(SUM(DailySalesRaw[SalesValue]),DailyStockData,UnitSize_PackSize)
Question:
What is the best way to do this:
Image of Data Model layout:
May 09 2021 05:39 PM
do you have sample of both the sales and the stock files with a few lines some with matching sites and barcode and some that does not match.
" Columns that need to match between the Stock and Sales data are Site and Barcode."
it sounds to me that you need to do a:
SUMIF StockTable[Site] = SalesTable[Site]
AND StockTable[Barcode] = SalesTable[Barcode]
in power query to normalize your data model and get a SUM of Total Sales from both files. Does that sound about right?