I'm trying to use a formula in Power Pivot's Data model to calculate totals from a fact table (Sales), based on 2 columns (basically create a virtual table to lookup), then add those totals to a Pivot table (created from a different fact table (Stock)), if those 2 columns match.
To break it down:
I have 2 fact tables (Sales and Stock) with multiple columns
Cannot create a relationship because it's many-to-many
I want to add the total sales to the stock pivot table
Do not want to create a separate dimension table (since this will need constant updating)
SALES Table(I want to get the sum of Sales when Barcode and Site is the same, thus disregard any other columns)
STOCK Pivot Table
Sales Totalin green is what it should be (it was added manually to show what the value should be)
Salesshould be the same asSales Total. Currently it's summing all sales regardless ofBarcodeandSite.
I useSUMMARIZEto create the virtual table to lookup. This should consist of Site, Barcode and sum of Sales from the Sales table. Thus removing duplicates for a combination ofSiteandbarcode.
TheALLEXCEPTis to ignore all row context and filters, except theSiteandBarcodefrom the Stock table.
I'm fairly new to Power Pivot and DAX formulas, so there might be an easier solution. I've been struggling with this for weeks trying different variations of formulas and switching between calculated columns and measures, but with no luck.