Price calculation from weight range

[giorgionway]

hello,

is it possible  to calculate sales price (in yellow cell) from PRICES table?

[Anonymous · Accepted solution]

Hi @giorgionway ,

First, you can create two calculated columns to get min weight and max weight of weight range:

minWeight = value(left('Prices'[Weight Range], SEARCH("-",'Prices'[Weight Range])-1))

maxWeight = value(mid('Prices'[Weight Range],SEARCH("-",'Prices'[Weight Range])+1,SEARCH("k",'Prices'[Weight Range])-2-SEARCH("-",'Prices'[Weight Range]) ))

Then create a measure to get the corresponding price:

sPrices = CALCULATE (
    MAX ( 'Prices'[Price] ),
    FILTER (
        'Prices',
        'Prices'[Company] = MAX ( 'Sales'[Company] )
            && 'Prices'[Level] = MAX ( 'Sales'[Level] )
            && 'Prices'[minWeight] <= MAX ( 'Sales'[Weight] )
            && 'Prices'[maxWeight] >= MAX ( 'Sales'[Weight] )
    )
)

Best Regards

Rena

[Greg_Deckler]

You could get there with some fancy string parsing DAX but what I would recommend is that in Power Query, you split your Weight Range into 3 columns. Split on "-" then split the second resulting column on " ". Then you have the mins and maxes in two separate columns and you could do this:

Price Column =
  VAR __Company = 'Sales'[Company]
  VAR __Level = 'Sales'[Level]
  VAR __Weight = 'Sales'[Weight]
RETURN
  MAXX(
    FILTER(
      'Prices',
      'Prices'[Company] = __Company && 'Prices'[Level] = __Level &&
        'Prices'[Min Weight Range] <= __Weight && 'Prices'[Max Weight Range] >= __Weight
    ),
    [Price]
  )