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)

Expected results:

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 Total in green is what it should be (it was added manually to show what the value should be)
Sales should be the same as Sales Total. Currently it's summing all sales regardless of Barcode and Site.

Current formula

Please Login or Register  to view this content.
I use SUMMARIZE to create the virtual table to lookup. This should consist of Site, Barcode and sum of SalesValue from the Sales table. Thus removing duplicates for a combination of Site and barcode.

The ALLEXCEPT is to ignore all row context and filters, except the Site and Barcode from 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.