Hi @akmughal,
The issue is that you are trying to allocate operating expenses by ASIN/Brand using purchase quantity as the allocation key but your denominator calculation is removing filters incorrectly, So let me tell you the Problem Analysis Then the Approach
Problem Analysis
- You dont have a direct relationship between the FBA Revenue Report and ASIN/Brand tables
- The REMOVEFILTERS approach is too aggressive and removes necessary filters
- Using a lookup value from Brand table without proper relationships causes issues
First Approach: Clean DAX (Recommended)
- Create this allocation measure:
Allocated Amazon Operating Expense :=
VAR CurrentAmazonExp = [Amazon Operating Expense] // This measure should filter for Operating Expenses
VAR CurrentDateContext = VALUES(DimDate[Date]) // Get current date context
// Calculate quantity for current context (ASIN, Brand, Date)
VAR ContextQty =
CALCULATE(
SUM(Purchases[Quantity]),
TREATAS(CurrentDateContext, Purchases[Date])
)
// Calculate total quantity for same date period
VAR TotalQty =
CALCULATE(
SUM(Purchases[Quantity]),
ALL(ASINMasterList),
ALLEXCEPT(Purchases, Purchases[Date]) // Keep date filter only
)
// Allocation formula
VAR AllocationRatio = DIVIDE(ContextQty, TotalQty, 0)
RETURN
IF(
NOT ISBLANK(CurrentAmazonExp) && TotalQty > 0,
CurrentAmazonExp * AllocationRatio,
BLANK()
)
Second Approach: Enhanced Model + DAX (Better For Long Term)
- First Create Proper Dimension Tables (Dim_ASIN table):
// Create a clean Dim_ASIN table
Dim_ASIN =
SUMMARIZE(
ASINMasterList,
ASINMasterList[ItemKey],
ASINMasterList[ASIN],
ASINMasterList[SKU],
ASINMasterList[Brand]
)
- Second Create Relationships:
- Dim_ASIN[ItemKey] → Purchases[Purchase_ItemKey] (1:*, Active)
- Dim_Date[Date] → Purchases[Date] (1:*, Active)
- Dim_Date[Date] → 'FBA Revenue Report'[Date] (1:*, Inactive)
- Create a separate Dim_Brand table with proper relationships
- Finally Enhanced Allocation Measure
Allocated Operating Expense v2 :=
VAR OperatingExpense =
CALCULATE(
SUM('FBA Revenue Report'[Amount]),
'FBA Revenue Report'[AccountType] = "Operating Expense"
)
VAR SelectedDates = VALUES(DimDate[Date])
// Quantity for current context (respects all filters)
VAR ContextQty =
CALCULATE(
SUM(Purchases[Quantity]),
TREATAS(SelectedDates, Purchases[Date])
)
// Total quantity for same dates, removing ASIN/Brand filters
VAR TotalQty =
CALCULATE(
SUM(Purchases[Quantity]),
TREATAS(SelectedDates, Purchases[Date]),
ALL(Dim_ASIN), // Remove ASIN filters
ALL(Dim_Brand) // Remove Brand filters
)
RETURN
DIVIDE(
OperatingExpense * ContextQty,
TotalQty,
BLANK()
)
Third Approach: Cross-Filtering (No Model Changes)
- If you won't/can't modify the model use this approach:
Allocated Expense CrossFilter :=
VAR OperatingExpense =
CALCULATE(
SUM('FBA Revenue Report'[Amount]),
'FBA Revenue Report'[AccountType] = "Operating Expense"
)
// Use TREATAS to bridge the gap between SKUs
VAR RelatedSKUs = VALUES(ASINMasterList[SKU])
VAR RelatedDates = VALUES(DimDate[Date])
// Calculate purchase quantity for related SKUs and dates
VAR ContextQty =
CALCULATE(
SUM(Purchases[Quantity]),
TREATAS(RelatedDates, Purchases[Date])
)
// Total quantity calculation
VAR TotalQty =
CALCULATE(
SUM(Purchases[Quantity]),
TREATAS(RelatedDates, Purchases[Date]),
ALL(ASINMasterList),
USERELATIONSHIP(Purchases[Brand], Brand[Brand]) // Explicitly use brand relationship
)
RETURN
IF(
NOT ISBLANK(OperatingExpense) && TotalQty > 0,
OperatingExpense * DIVIDE(ContextQty, TotalQty),
BLANK()
)
Additional Helper Measures:
// Amazon Operating Expense (base measure)
Amazon Operating Expense :=
CALCULATE(
SUM('FBA Revenue Report'[Amount]),
'FBA Revenue Report'[AccountType] = "Operating Expense"
)
// Purchase Quantity (with proper context)
Purchase Quantity :=
SUM(Purchases[Quantity])
// Total Purchase Quantity (for allocation denominator)
Total Purchase Quantity :=
CALCULATE(
[Purchase Quantity],
ALL(ASINMasterList),
ALLEXCEPT(Purchases, Purchases[Date])
)
Final Note:
- The Second Approach (enhanced model) will give you the best performance and maintainability, but the First Approach should work with your current model after adjusting the denominator calculation
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
