Forum Discussion
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...
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
Super User
Super Useris that because we don't have the suggested price?
if no suggested price, then always be 27.2?
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
pls try
if (isblank(suggested price), minx(values(product),suggestedprice ), max(suggested price))