Lookup nearest match on multiple columns same table

[JeroenHD]

Hello Power BI fanatics, 

i have this challenge and i can't get my head around on how to solve it. Preferably the solution i'm looking for would be Power Query, but Dax would also be much appreciated. 

The table below shows several part id's with an required quantity next to it. I would like to add another column which is the price i would be paying. The price is graduated. If the Qty = 1 than it should return the Default Price. Any other Qty > 1 should be evaluated against the QuantityBreaks and find the closest match. Then multiply the quantity agains the related Unitprice. 

Does anybody have an idea on how to do this? 

Kind regards,
Jeroen

[DataInsights · Accepted solution]

@JeroenHD,

Try this calculated column (DAX):

Price = 
VAR vQty = Parts[Qty]
VAR vPriceToUse =
    SWITCH (
        TRUE (),
        vQty >= Parts[QuantityBreak3], Parts[UnitPrice3],
        vQty >= Parts[QuantityBreak2], Parts[UnitPrice2],
        vQty >= Parts[QuantityBreak1], Parts[UnitPrice1],
        Parts[DefaultPrice]
    )
VAR vResult = vQty * vPriceToUse
RETURN
    vResult

[ryan_mayu · Accepted solution]

@JeroenHD 

you can try to use DAX to create a new column

Column = 
VAR qb1=ABS('Table'[Qty]-'Table'[QuantityBreak1])
VAR qb2=ABS('Table'[Qty]-'Table'[QuantityBreak2])
VAR qb3=ABS('Table'[Qty]-'Table'[Quantitybreak3])
VAR result=min(min(qb1,qb2),qb3)
return if('Table'[Qty]=1,'Table'[DefaultPrice],if(result=qb1,'Table'[Unitprice1],if(result=qb2,'Table'[Unitprice2],'Table'[Unitprice3])))

please see the attachment below.

[DataInsights · Accepted solution]

@JeroenHD,

You can use the ISBLANK function to ignore blanks:

Price = 
VAR vQty = Parts[Qty]
VAR vPriceToUse =
    SWITCH (
        TRUE (),
        vQty >= Parts[QuantityBreak3] && NOT ISBLANK ( Parts[QuantityBreak3] ), Parts[UnitPrice3],
        vQty >= Parts[QuantityBreak2] && NOT ISBLANK ( Parts[QuantityBreak2] ), Parts[UnitPrice2],
        vQty >= Parts[QuantityBreak1] && NOT ISBLANK ( Parts[QuantityBreak1] ), Parts[UnitPrice1],
        Parts[DefaultPrice]
    )
VAR vResult = vQty * vPriceToUse
RETURN
    vResult

[JeroenHD]

This works like a charm. What i forgot to mention is that Quantity breaks and Unit prices are not necessarily filled. Does anyone know how to ignore blanks in the Switch function?

[DataInsights · Accepted solution]

@JeroenHD,

Here's the Power Query version:

if [Qty] >= [QuantityBreak3] and [QuantityBreak3] <> null then
        [Qty] * [UnitPrice3]
      else if [Qty] >= [QuantityBreak2] and [QuantityBreak2] <> null then
        [Qty] * [UnitPrice2]
      else if [Qty] >= [QuantityBreak1] and [QuantityBreak1] <> null then
        [Qty] * [UnitPrice1]
      else
        [Qty] * [DefaultPrice]

[JeroenHD]

Thanks @DataInsights this works perfectly! Didn't think of categorizing it. 

[DataInsights · Accepted solution]

@JeroenHD,

You can use the ISBLANK function to ignore blanks:

Price = 
VAR vQty = Parts[Qty]
VAR vPriceToUse =
    SWITCH (
        TRUE (),
        vQty >= Parts[QuantityBreak3] && NOT ISBLANK ( Parts[QuantityBreak3] ), Parts[UnitPrice3],
        vQty >= Parts[QuantityBreak2] && NOT ISBLANK ( Parts[QuantityBreak2] ), Parts[UnitPrice2],
        vQty >= Parts[QuantityBreak1] && NOT ISBLANK ( Parts[QuantityBreak1] ), Parts[UnitPrice1],
        Parts[DefaultPrice]
    )
VAR vResult = vQty * vPriceToUse
RETURN
    vResult

[JeroenHD]

This is also a great solution. I can either take care of it in Dax or M now! Thanks for reaching the helping hand!

[JeroenHD]

@DataInsights & @ryan_mayu thank you so much. You are life savers! Cool to see there's more than one way to do it! Thanks again! Now i'm back into the books to see what you actually did:-)

[ryan_mayu · Accepted solution]

@JeroenHD 

you can try to use DAX to create a new column

Column = 
VAR qb1=ABS('Table'[Qty]-'Table'[QuantityBreak1])
VAR qb2=ABS('Table'[Qty]-'Table'[QuantityBreak2])
VAR qb3=ABS('Table'[Qty]-'Table'[Quantitybreak3])
VAR result=min(min(qb1,qb2),qb3)
return if('Table'[Qty]=1,'Table'[DefaultPrice],if(result=qb1,'Table'[Unitprice1],if(result=qb2,'Table'[Unitprice2],'Table'[Unitprice3])))

please see the attachment below.

[DataInsights · Accepted solution]

@JeroenHD,

Try this calculated column (DAX):

Price = 
VAR vQty = Parts[Qty]
VAR vPriceToUse =
    SWITCH (
        TRUE (),
        vQty >= Parts[QuantityBreak3], Parts[UnitPrice3],
        vQty >= Parts[QuantityBreak2], Parts[UnitPrice2],
        vQty >= Parts[QuantityBreak1], Parts[UnitPrice1],
        Parts[DefaultPrice]
    )
VAR vResult = vQty * vPriceToUse
RETURN
    vResult