Forum Discussion

cryspezz's avatar
cryspezz
Frequent Visitor
3 years ago
Solved

Lookup Closest Suggested Price

Hello,   I am working on a project that helps our Pricing team identify areas where a customer isn't following our suggested pricing.   We offer a suggested pricing structure to the wholesaler, w...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    cryspezz 

    you can try this

    Measure = 
VAR _low=maxx(FILTER('Suggested Price','Suggested Price'[Date]=max('Sales'[Date])&&'Suggested Price'[Customer]=max(Sales[Customer])&&'Suggested Price'[Product]=max(Sales[Product])&&'Suggested Price'[Suggested Price]<=max(Sales[ Sales Price ])),'Suggested Price'[Suggested Price])
VAR _high=minx(FILTER('Suggested Price','Suggested Price'[Date]=max('Sales'[Date])&&'Suggested Price'[Customer]=max(Sales[Customer])&&'Suggested Price'[Product]=max(Sales[Product])&&'Suggested Price'[Suggested Price]>=max(Sales[ Sales Price ])),'Suggested Price'[Suggested Price])
VAR _diff1=ABS(_low-max(Sales[ Sales Price ]))
VAR _diff2=ABS(_high-max(Sales[ Sales Price ]))
return if (_diff1<=_diff2,_low,_high)


Measure 2 = [Measure]-max(Sales[ Sales Price ])

     

    pls see the attachment below

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
3 years ago

cryspezz 

you can try this

Measure = 
VAR _low=maxx(FILTER('Suggested Price','Suggested Price'[Date]=max('Sales'[Date])&&'Suggested Price'[Customer]=max(Sales[Customer])&&'Suggested Price'[Product]=max(Sales[Product])&&'Suggested Price'[Suggested Price]<=max(Sales[ Sales Price ])),'Suggested Price'[Suggested Price])
VAR _high=minx(FILTER('Suggested Price','Suggested Price'[Date]=max('Sales'[Date])&&'Suggested Price'[Customer]=max(Sales[Customer])&&'Suggested Price'[Product]=max(Sales[Product])&&'Suggested Price'[Suggested Price]>=max(Sales[ Sales Price ])),'Suggested Price'[Suggested Price])
VAR _diff1=ABS(_low-max(Sales[ Sales Price ]))
VAR _diff2=ABS(_high-max(Sales[ Sales Price ]))
return if (_diff1<=_diff2,_low,_high)


Measure 2 = [Measure]-max(Sales[ Sales Price ])

 

pls see the attachment below

cryspezz's avatar
cryspezz
Frequent Visitor
3 years ago

Hi Ryan,

 

Thanks so much for the DAX on this, it's working pretty closely to what we'd hope to see as a result.  There are a few scenarios where I'm returning blanks for the lowest Sales Price.

 

Any recommendation for capturing $27.20 for this example below at the $12.00 Sales Price?

 

 

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    cryspezz 

    is that because we don't have the suggested price? 

    if no suggested price, then always be 27.2?

    • cryspezz's avatar
      cryspezz
      Frequent Visitor
      3 years ago

      The suggested price at the $12.00 sales price should return $27.20 since it's the lowest suggested price point offered.  It works down to the $26 sales price, but after that it's returning a blank.

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        3 years ago

        pls try 

        if (isblank(suggested price), minx(values(product),suggestedprice ), max(suggested price))