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

[JoshP11]

Hello - wondering if anyone can assist with the below scenario...

I have 3 sharepoint lists holding data and connected to PBI. I have created a relationship between Product Name on Gifting List and Restocking List.

1. Stock List – Contains current inventory.
2. Gifting List – Tracks items gifted (and their cost).
3. Restocking List – Tracks items restocked (and their cost).

Gifting list tracks Units Gifted, Total Cost.

Restocking List tracks Quantity Restocked, Unit Cost, Total Cost.

The Problem I'm facing

If you:

• Restock an item (cost is recorded in the Restocking List),
• Then gift that same item (cost is recorded again in the Gifting List),

When calculating total spend, from gifting and restock, there may be double counting.

The Goal

Total Spend = Cost of Gifting + Cost of Restocking − Cost of Gifted Items That Were Previously Restocked

Is there an easy way to calculate this in a measure / multiuple measures?

Thanks!

[v-pnaroju-msft]

Thankyou, @pankajnamekar25, for your response.

Hi @JoshP11,

We sincerely appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Please find attached the screenshot and the PBIX file, which we hope will assist you in resolving the issue:

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will help other community members who might be facing similar queries.

Should you have any further questions or require additional assistance, please feel free to contact the Microsoft Fabric Community.

Thank you.

[Ashish_Excel]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[pankajnamekar25 · Accepted solution]

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.