Formula help for summing by lowest cost

[Anonymous]

Hello all,

I wanted to see if I could get some help with the below table. I have line items that share the same part numbers, but have different stores and customers. I want to create 3 additonal columns that bring back the lowest cost, the store associated with that and customer with that.

So for example, PN-546 has two prices. I want line there to be 3 columns to the righ that would come back like the table below the first one. Any help is much appreciated!

Customer	Store	Part Number	Price
A	North	PN-123	0.056
B	South	PN-546	1
C	West	PN-546	1.5
D	East	PN-222	0.047

Customer	Store	Part Number	Price	Lowest Cusomter	Lowest Store	Price
A	North	PN-123	0.056	A	North	$     0.06
B	South	PN-546	1	B	South	$     1.00
C	West	PN-546	1.5	B	South	$     1.00
D	East	PN-222	0.047	D	East	$     0.05

[Greg_Deckler · Accepted solution]

@Anonymous Try these:

Lowest Customer Column = 
  VAR __StorePart = [Store Part]
  VAR __Lowest = MINX(FILTER(ALL('Table'),[Store Part] = __StorePart ),[Price])
  VAR __Result = MINX(FILTER(ALL('Table'),[Store Part] = __StorePart && [Price] = __Lowest ),[Customer])
RETURN
  __Customer


Lowest Store Column = 
  VAR __StorePart = [Store Part]
  VAR __Lowest = MINX(FILTER(ALL('Table'),[Store Part] = __StorePart ),[Price])
  VAR __Result = MINX(FILTER(ALL('Table'),[Store Part] = __StorePart && [Price] = __Lowest ),[Store Part])
RETURN
  __Customer



Lowest Price Column = 
  VAR __StorePart = [Store Part]
  VAR __Lowest = MINX(FILTER(ALL('Table'),[Store Part] = __StorePart ),[Price])
RETURN
  __Lowest

[Anonymous]

Hello @Greg_Deckler ,

Thank you for your help, those all worked perfeclty! Wold you be able to tell me how to calcualte the impact if we changed over to that lowest price?

For example the table below. Essentially, if there is a lower price, I want to be able to multiply that line item by iteself by the amount we are buying on that line item. I don't want to sum the amount we buy in total, just that line item.

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