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,

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.