Lookup Closest Suggested Price

[cryspezz]

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, which isn't always necessarily followed.  I need to be able to show the nearest Suggested Price (rounding up for all pricing unless it's above the highest price, and in that case rounding down) for that particular day, customer, product, side by side with the Sales Price.  I've tried a TOPN formula, but I can't seem to get it to work.

Sales Fact Table:

Date	Customer	Product	Sales Price	Units	Measure Output: Suggested Price	Measure Output: Difference in Actual vs Suggested
1/1/2022	X	A	$        21.99	10	$                             20.99	$                                    (1.00)
1/3/2022	X	A	$        20.00	35	$                             20.99	$                                0.99
1/7/2022	X	A	$        19.50	60	$                             19.99	$                                0.49
1/15/2022	X	A	$        17.99	100	$                             19.99	$                                2.00
1/21/2022	X	A	$        16.99	5	$                             16.99	$                                    -
1/31/2022	X	A	$        15.99	55	$                             15.99	$                                    -

Suggested Price Table:

Date	Customer	Product	Qty	Suggested Price
1/1/2022	X	A	1	20.99
1/1/2022	X	A	50	19.99
1/3/2022	X	A	1	20.99
1/3/2022	X	A	50	19.99
1/7/2022	X	A	1	20.99
1/7/2022	X	A	50	19.99
1/15/2022	X	A	1	20.99
1/15/2022	X	A	50	19.99
1/21/2022	X	A	1	16.99
1/21/2022	X	A	50	15.99
1/31/2022	X	A	1	16.99
1/31/2022	X	A	50	15.99

[Ashish_Mathur]

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]

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]

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]

Hi Ashish,

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

[ryan_mayu · Accepted solution]

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

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]

@cryspezz 

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

if no suggested price, then always be 27.2?

[cryspezz]

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

[cryspezz]

I changed the result to the below and it seemed it filled in the suggested price correctly.  Thanks again for your help!

SWITCH(

    TRUE(),

    [diff1] <= [High PTR], [High PTR], [Low PTR])

[ryan_mayu]

you are welcome