Forum Discussion

giorgionway's avatar
giorgionway
Frequent Visitor
6 years ago
Solved

Price calculation from weight range

hello, is it possible  to calculate sales price (in yellow cell) from PRICES table?      
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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]
  )