Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to Solution.
@Kfitzek2022 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
@Kfitzek2022 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
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 |
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |