How to get closest Warehouse for each Sales Order?

[Anonymous]

Hello Friends, 

I am not sure this is right place to ask, but I need an idea or help on how to do this. I am looking to create an Inventory report which gives me the closest warehouse to my sales order. Sales orders are coming around the US, but I have only 3 warehouses now. 

All I need to show is which warehouse is closest to sales order zip code. 

I have 2 main tables.  I can add another table with zip codes for all the state in US, but I am not sure how to achive it. Any  idea will be really appricated. (If I can make it dynamic, that will be ideal. When we add new warehouse it automatically adjust)

 Thank you so much

[v-diye-msft]

Hi @Anonymous 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[Ashish_Mathur]

Hi,

I am confused.  What exact result are you expecting?  How does one determine the "closest warehouse to a sales order"?

[Anonymous]

Hi @Ashish_Mathur 

Thank you, the only thing I have in my mind is distance calculated by zip code  for each warehouse and sales order zip code, then get the lowest or manually create 3 groups of zip codes that are closed to each warehouse. 

Again, I am not sure the above may not be possible. I just want some expert idea how to do this. I never face this kinda problem. So kinda brainstorming ideas, how to do this easiest possible way. Please let me know if you know any other easy way. 

Note -  I have added another table with expected results. 

Thank you so much

[Sumanth_23 · Accepted solution]

hi @Anonymous - You would need to bring in another table which has distance between possible sales order locations and each warehouse - so in your case each potential location of sales will have 3 records in this "distance" table

You can then rank the distance for each sales order location and then display warehouse for sales order where rank = 1  

Rank-Distance =

VAR _Rnk =

RANKX (

FILTER (

'Warehouse-Dist',

'Warehouse-Dist'[Sales Loc] = EARLIER ( 'Warehouse-Dist'[Sales Loc] )

&& 'Warehouse-Dist'[Distance] <> BLANK ()

),

'Warehouse-Dist'[Distance],

,

ASC,

DENSE

)

RETURN

IF ( 'Warehouse-Dist'[Distance] = BLANK (), BLANK (), _Rnk )

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[Anonymous]

Hi @Sumanth_23  ,

I am getting an error. I know it something on my side. I am not familer with funtion Power BI. Can you please tell me what I am doing wrong here? 

Rank_Distance = 
    VAR_Rnk =
        RANKX(
        FILTER(
            Main, 
            Main[Ship_City] = EARLIER(Main[Ship_City]) 
            && Main[Distance] <> BLANK()
            ),
        Main[Distance],
        ASC, 
        DENSE)
    RETURN
        IF(Main[Distance] = BLANK(), BLANK(),_Rnk)

Error - The syntax for 'RETURN' is incorrect. (DAX( VAR_Rnk = RANKX( FILTER( Main, Main[Ship_City] = EARLIER(Main[Ship_City]) && Main[Distance] <> BLANK() ), Main[Distance], ASC, DENSE) RETURN IF(Main[Distance] = BLANK(), BLANK(),_Rnk))).

Sample data of my table

Code = Warehouse
Shipcity = Sales Loc

Thank you so much for your help and time! 

[Sumanth_23]

hi @Anonymous - There were a couple of syntax errors but on fixing them the column is calculating as expected 

1. There is SPACE between VAR & _Rnk on line 2

2. There is an additional "," before "ASC,"

Rank_Distance =

VAR _Rnk =

RANKX(

FILTER(

'Warehouse-Distance',

'Warehouse-Distance'[Ship_City]= EARLIER('Warehouse-Distance'[Ship_City])

&& 'Warehouse-Distance'[Distance] <> BLANK()

),

'Warehouse-Distance'[Distance],

,

ASC,

DENSE)

RETURN

IF('Warehouse-Distance'[Distance] = BLANK(), BLANK(), _Rnk)

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[Anonymous]

Hi @Sumanth_23 

Thank you I was able to fix the formula, but I think it is not grouping correctly on my one. I see on your picture rank is 1,2,3 for each code (Warehouse) by sales location distance. My one does not show like that.  

I am really sorry to bug you, but I think I am really close. Do you know why it that? Please see below, I have selected a few sales orders for your reference. 

I even try to swich Ship cit with Code (Warehouse), did not work. 

Rank_Distance = 
    VAR _Rnk =
        RANKX(
        FILTER(
            Main, 
            Main[Ship_City] = EARLIER(Main[Ship_City]) 
            && Main[Distance] <> BLANK()
            ),
        Main[Distance],,
        ASC, 
        DENSE)
    RETURN
        IF(Main[Distance] = BLANK(), BLANK(),_Rnk)

Thank you so much

[Sumanth_23]

hi @Anonymous - That is strange - can you check the data types of all columns in your data model. Also I have uploaded the pbix file to the below path - you can take a look and see what is different when compared to your model

https://drive.google.com/file/d/1DiwHnohFxA-dbALMZR0Glkqg_55ifpP_/view?usp=sharing

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[Anonymous]

Hi @Sumanth_23 

I am matching data format as it like on your file. I have no idea why it doing like that since I have samething like you. I have uploaded my sample pibix file. I would appreciate you could take look. Thank you so much for the help.  There should be something I am missing. Again, million thanks for your help. 

https://drive.google.com/file/d/1zQajuEdbPM-RLQYn5Nd8KR-YewFFvHFx/view?usp=sharing

[Sumanth_23]

hi @Anonymous - It appears you have data for other values for "Code" which you are filtering out at visual level - since rank is being calculated within your table it is based on the data available. 

Please see below if all codes are included then the rank is being evaluated as expected  

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[Anonymous]

@Sumanth_23  Thank you so much for your hlpe and time. You are awesome. !! This works for me. I am going to filter it out on my table level. It should work on ranking then. Again, thank you 

[Anonymous]

@Sumanth_23  Thank you so much, I will try this. 

[Sumanth_23]

hi @Anonymous - Happy to help! 🙂

[Ashish_Mathur]

Hi,

I do not understand.  May be someone else will help you.