Forum Discussion
Helper IIAvoiding Double Counting in Power BI: Gifting vs. Restocking Costs
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.
- Stock List – Contains current inventory.
- Gifting List – Tracks items gifted (and their cost).
- 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!
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 | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
3 Replies
- pankajnamekar25
Super User
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 | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
v-pnaroju-msftCommunity Support
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_ExcelSolution Supplier
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.