Mar 02 2021 06:34 AM
Hi everyone,
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:
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
Current formula
Sales:=CALCULATE(CALCULATE(SUM(DailySalesRaw[SalesValue]),SUMMARIZE(DailySalesRaw,DailySalesRaw[BarcodeCode],DailySalesRaw[Site],DailySalesRaw[SalesValue])),ALLEXCEPT(DailyStockData,DailyStockData[BarcodeCode],DailyStockData[Site]))
I use SUMMARIZE to 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 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.