Forum Discussion
Formula help for summing by lowest cost
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 |
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
2 Replies
- Greg_Deckler
Community Champion
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 __LowestHello 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