Map - Postal Code - Client Pattern Shift

[Anonymous]

Hi all, I am trying to find out if there is a way I could show a shift in client pattern. For instance, I have 5 offices, and we are planning to close an office; which office would client choose to travel based on previous postal code data. The sample postal code data looks similar to this (except there are more than 15,000 postal codes record for last year)

On Excel, I would do a percentage of row, and distribute client from Office that we are closing to other offices. Example, if I am closing Office 1, I would find the percentage of the other rows and see WHO gets the most client. But I am hoping to do this visually? Any idea to accomplish this task. I am just trying to see the change in client's pattern when office closes; which one of these OTHER offices the client would go to. Thanks

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

If i understand corrrectly, you may achieve below.

If 'Office 1' is colsed, for 'T0B2X0':

The value of 'Office 2'=1+58*(1/(1+32+7+9))
The value of 'Office 3'=32+58*(32/(1+32+7+9))
The value of 'Office 4'=7+58*(7/(1+32+7+9))
The value of 'Office 5'=9+58*(9/(1+32+7+9))

I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Result = 
var tab = 
CALCULATETABLE(
    DISTINCT('Table'[Office]),
    ALLSELECTED('Table')
)
var sumcolse = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table','Table'[Postal Code]),
        NOT([Office] in tab)
    )
)
var sumnotcolse = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table','Table'[Postal Code]),
        [Office] in tab
    )
)
return
SUM('Table'[Value])+sumcolse*
DIVIDE(
    SUM('Table'[Value]),
    sumnotcolse
)

Result(When 'Office 1' is not selected, other offices add values by the percentage):

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much!! that works!!