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

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

Hi,

Ideally you should solve this with a calculated column formula in the Sales Fact table rather than with a measure.  If you are OK with my approach, then let me know.  Also, shouldn't the answer in row 2 of the dataset be 19.99 (35 is closer to 50 than it is to 1).  Please clarify.

cryspezz's avatar
cryspezz
Frequent Visitor
3 years ago

Hi Ashish,

 

I'd like to hear more about the calculated column approach.  The suggested qty can be ignored as the customer can technically sell at any price they'd like.

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

    Hi,

    This calculated column formula returns the suggested price where quantity is 1 on the suggested_price table.

    =CALCULATE(MAX(Suggested_price[Suggested Price]),FILTER(Suggested_price,Suggested_price[Date]=EARLIER(Sales[Date])&&Suggested_price[Customer]=EARLIER(Sales[Customer])&&Suggested_price[Product]=EARLIER(Sales[Product])&&Suggested_price[Qty]=1))

    Hope this helps.

    • cryspezz's avatar
      cryspezz
      Frequent Visitor
      3 years ago

      Hi Ashish,

       

      This calculated column looks like it is giving us the highest suggested price and not the closest.