Input Sample Data:
| Customer | SKU | Date | Rank | Quantity |
| A | S100 | Jan-24 | 1 | 200 |
| A | S100 | Oct-24 | 2 | 400 |
| A | S200 | Nov-24 | 3 | 45 |
| A | S100 | Mar-25 | 4 | 100 |
| A | S100 | Oct-25 | 5 | 200 |
| A | S200 | Oct-25 | 5 | 67 |
| B | S100 | Jan-24 | 1 | 214 |
| B | S100 | Oct-24 | 2 | 412 |
| B | S100 | Mar-25 | 4 | 112 |
| B | S100 | Oct-25 | 5 | 211 |
| B | S200 | Oct-25 | 5 | 78 |
| B | S300 | Oct-25 | 5 | 55 |
Output Sample Data:
| Customer | SKU | CY 2025 | CY Mix % |
| A | S100 | 300 | 82% |
| A | S200 | 67 | 18% |
| B | S100 | 323 | 100% |
Explanantion of Output Table:
1. Quantity is aggregated for Customer and SKU for current year and previous year (CY) vs (PY) using input table.
2. For every customer, only those SKUs are retained which are sold in both years (Naturalinnerjoin). Therefore, SKU S200 & S300 are not considered in output table as they are not present in both CY and PY.
3. Calculate Mix% for current year, within in each customer by dividing SKU level quantity by Customer level total quantity.
4. Rank column is according to date. The rank column from input table is used to filter time period dynamically.
5. I am not able to achieve total at customer level after Naturalinnerjoin in DAX.
DAX:
MnA =
VAR base_rank = SELECTEDVALUE(BaseCalendar[Rank])
VAR actual_rank = SELECTEDVALUE(ActualCalendar[Rank])
VAR timePeriod = SELECTEDVALUE(TimePeriod[SelectedPeriod])-1
// 1.
VAR tbl1 = SUMMARIZE( FILTER( CleanSales,
CleanSales[rank] >= base_rank - timePeriod &&
CleanSales[rank] <= base_rank ),
All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode],
"QuantityB", SUM(CleanSales[Quantity]))
// 2.
VAR tbl2 = SUMMARIZE( FILTER( CleanSales,
CleanSales[rank] >= actual_rank - timePeriod &&
CleanSales[rank] <= actual_rank ),
All_CustomerIDs[CustomerNo], All_SKUCode[SKUCode],
"QuantityA", SUM(CleanSales[Quantity]))
// 3.
VAR FullJoinTable2 = NATURALINNERJOIN(tbl1,tbl2)
// 4.
VAR CustomerTotals = ADDCOLUMNS(
SUMMARIZE(FullJoinTable2, All_CustomerIDs[CustomerNo]),
"TotalBaseQuantity", CALCULATE(SUMX(FullJoinTable2, [QuantityB])),
"TotalActualQuantity", CALCULATE(SUMX(FullJoinTable2, [QuantityA])))
// 5. Merge totals back
VAR FullWithTotals = NATURALLEFTOUTERJOIN(FullJoinTable2, CustomerTotals)
// 6.
VAR MAMB = ADDCOLUMNS(
FullWithTotals,
"Mn_A", DIVIDE([QuantityA], [TotalActualQuantity], 0),
"Mn_B", DIVIDE([QuantityB], [TotalBaseQuantity], 0))
// 7.
// Final calculation: Return the sum of Mn_B across all products in tbl1
RETURN AVERAGEX(MAMB, [Mn_A])
Brief explanation of DAX -
1. Data is filtered first time using Current time period: Dynamic filtering using current start date, previous start date and last-n-months slicers. I have used ranks to filter time periods below. Actual & Base calendars are not connected to sales table.
2. Same data filtered using Base time period.
3. Both of the datasets are inner joined at Customer x SKU level to find common combinations (Naturalinnerjoin). All_CustomerIDs, All_SKUCode are connected to sales table.
4. This table with common combinations is again summarized at customer level to find customer level total.
5. Table from point 4 is joined back on table in point 3 (inner-joined).
6. Calculate mix% by sku level qty/ customer level qty.
7. Return mix% using averagex for each Customer and SKU
Issues in DAX:
1. The calculation fails at point 4. i.e. not able to calculate customer level totals. Please suggest correct DAX.
2. Is return method correct?
3. Can this be optimized? any better method to achieve this type of calculation?
