Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Kfitzek2022
Frequent Visitor

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!

 

CustomerStorePart NumberPrice
ANorthPN-1230.056
BSouthPN-5461
CWestPN-5461.5
DEastPN-2220.047

 

CustomerStorePart NumberPriceLowest CusomterLowest StorePrice
ANorthPN-1230.056ANorth $     0.06
BSouthPN-5461BSouth $     1.00
CWestPN-5461.5BSouth $     1.00
DEastPN-2220.047DEast $     0.05
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

 

CustomerStorePart NumberPriceLowest CusomterLowest StoreLowest PriceTotal BoughtDeltaImpact
ANorthPN-1230.056ANorth $       0.056                3.00 $          -  0
BSouthPN-5461BSouth $         1.00                4.00 $          -  0
CWestPN-5461.5BSouth $         1.00              10.00 $     0.50 $     5.00
DEastPN-2220.047DEast $       0.047                4.00 $          -  0

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.