Help with measure or calculated column

[Chendoo]

I have three tables in Data Model Power Pivot for Excel 2019. One fact table named 'ProductionPalletSummaryNew'. One dimension table named 'Cases Per Pallet' and another dimension table named 'Lines'. ProductiionPalletSummaryNew table and Cases Per Pallet table related by ProductID key. ProductionPalletSummaryNew is related to Line table by Process Order key. I am using this measure

# of pallets:=SUMX(VALUES(ProductionPalletSummaryNew[ProductID]),SUM(ProductionPalletSummaryNew[Cases]))/SUM('Cases Per Pallet'[Cases Per Pallet])

named # of pallets to sum cases and it work fine. However, I want to be able to sum cases by [ProductionDate], [ProductID], [Shift], which are located within 'ProductionPalletSummaryNew' table and as well by [Line] which is located within 'Lines' table and finally divide the the number of cases by [Cases Per Pallet] field located in Cases Per Pallet table. I am able to accomplish this task using a VLOOKUP function

=IFERROR(E8/VLOOKUP(D8,tbl_Cases_Per_Pallet,3,FALSE),"0")

adjacent to a pivot table. However, I would prefer to use a measure or a calculated column to accomplish this end result. I would greatly appreciate your help and time. Below is a copy of the pivot table. I have also tried this calculated column, but I'm getting a huge number that doesn't seem right. I would greatly appreciate if you can help me to modify either the measure or calculated column to get end results in column G.

=SUMX(
    FILTER(
        ProductionPalletSummaryNew,
        ProductionPalletSummaryNew[ProductionDate] = EARLIER(ProductionPalletSummaryNew[ProductionDate])
    ),
    ProductionPalletSummaryNew[Cases]
) * RELATED('tbl_Cases_Per_Pallet'[Cases Per Pallet])