Avoiding Double Counting in Power BI: Gifting vs. Restocking Costs

Step-by-Step DAX Measures

Assume relationships are:

Product Name is common between tables.

All cost columns are numeric.

Restocking Total Cost

Restocking Cost = SUM('Restocking List'[Total Cost])

 Gifting Total Cost

Gifting Cost = SUM('Gifting List'[Total Cost])

Overlapping Gifted Cost (i.e. cost that would be double-counted)

This part assumes that the unit cost from the Restocking List is representative for the product (e.g., average unit cost if multiple restocks). You can calculate average like this:

Avg Unit Cost =

AVERAGEX(

    'Restocking List',

    'Restocking List'[Unit Cost]

)

Then

Overlapping Gifted Cost =

SUMX(

    'Gifting List',

    'Gifting List'[Units Gifted] *

    CALCULATE(

        AVERAGE('Restocking List'[Unit Cost]),

        FILTER(

            'Restocking List',

            'Restocking List'[Product Name] = 'Gifting List'[Product Name]

        )

    )

)

This estimates the cost of gifted items based on their restocked unit cost.

 Final Spend Measure

Total Spend (Adjusted) =

[Restocking Cost] + [Gifting Cost] - [Overlapping Gifted Cost]

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.