Summing Change on Line Item Level

[Anonymous]

Hello all, I'm looking to get some help with a formula to sum up change by line item level. Currently my formula will calculate the difference, but it does it by all parts involved in my data set. I want it to ignore the other instances of the part purchases and only sum the difference for the specific line item. Data set below for example. Customer C bought the same part as customer B, but at a higher price. I want my formula to calculate the differnce between the lowest price paid on that part and then multiply it by the total amount bought. Thank you in advance!

Customer	Store	Part Number	Price	Lowest Cusomter	Lowest Store	Lowest Price	Total Bought	Delta	Impact
A	North	PN-123	0.056	A	North	$0.06	3	$          -	0
B	South	PN-546	1	B	South	$1.00	4	$          -	0
C	West	PN-546	1.5	B	South	$1.00	10	$0.50	$5.00
D	East	PN-222	0.047	D	East	$0.05	4	$          -	0

[Sahir_Maharaj · Accepted solution]

Hello @Anonymous,

To calculate the difference between the lowest price paid on a part and the price paid by each customer for that part:

Delta = 
VAR MinPrice = CALCULATE(MIN('Table'[Price]), ALLEXCEPT('Table', 'Table'[Part Number]))
RETURN
'MyTable'[Price] - MinPrice

To calculate the impact of the price difference on the total amount bought:

Impact = 'Table'[Delta] * 'Table'[Total Bought]

Let me know if you might require further assistance.