Cross reference customer number to alternate customer numbers to accurately track sales

[jaambor]

Hello Power BI community,

  First off, thanks for taking the time to look at my issue, second of all I am google trained with Power BI, and might be asking a rookie level question, so I apologize in advance. 

 I am trying to figure out how solve a problem with linking customer numbers and alternate numbers.  When I track sales, I want the sales that have the Vendor 1 (one time buyer) and Vendor 2 (Subscription ) numbers to be reflected back on my internal number for ease of tracking. 

I can’t use the other vendor numbers as many times they duplicate or frequently reuse the numbers and I wanted to have a clean lookup table to use.  I have no ownership of the process, just trying to make it less time consuming so I can just import the excel docs and be done.

All of our customers have a unique internal number (N1234) so I wanted to use these as a lookup table.  The problem is both of our vendors assign an individual number of their own to the same customers. And sometimes there is only one alternate number

Our Internal #          Vendor 1         Vendor 2

N1234                   S9876                    H4567

N1235                   S9875                    --

N1236                   --                           H4568

N1237                   S9873                    H4569

N1238                   S9872                    H4560

Here is how my excel download looks like:

Cust ID                  Item                      Qty         Cost      

S9873                    124578                  2              50.00

H4568                    235689                  1              25.00

S9876                    235689                  4              100.00

I would like my Power BI to show a chart where I can have an output:

Customer            Items Ordered         Sales

N1234                   4                            100.00

N1237                   2                             50.00

N1236                   1                             25.00

Thank you all for your assistance and expert advice.

  Jeremy

[parry2k]

@jaambor I will not go with the solution provided by @v-luwang-msft . In my opinion, it is not a scalable solution.

What @bcdobbs  is provided the way to go. Transform the vendor table and finish what he started:

once the vendor table is created, close and apply, set the relationship on the vendor id column with the sales table and that's it. from there, in any visual use internal id from this new table and sum of sales, or sum of orders, count of orders, any measure it would work.

The other worst part of @v-luwang-msft solution is that if you have to add a new measure in the feature, you have to do that conditional checking every time and it will have potential performance issues, so NOT AT ALL SCALABLE. Good luck! 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[v-luwang-msft · Accepted solution]

Hi @jaambor ,

Pls create new column on base table ,then create new table on the base table and new column:

Items Ordered = 
IF (
    LOOKUPVALUE ( Table2[Qty], Table2[Cust ID], Table1[Vendor 1] ) <> BLANK (),
    LOOKUPVALUE ( Table2[Qty], Table2[Cust ID], Table1[Vendor 1] ),
    LOOKUPVALUE ( Table2[Qty], Table2[Cust ID], Table1[Vendor 2] )
)

Sales = 
IF (
    LOOKUPVALUE ( Table2[Qty], Table2[Cust ID], Table1[Vendor 1] ) <> BLANK (),
    LOOKUPVALUE ( Table2[Cost], Table2[Cust ID], Table1[Vendor 1] ),
    LOOKUPVALUE ( Table2[Cost], Table2[Cust ID], Table1[Vendor 2] )
)

Then create new table:

Table3 = SELECTCOLUMNS(Table1,"ID",Table1[Our Internal #],"Items Ordered",Table1[Items Ordered],"Sales",Table1[Sales])

You could download my pbix file if you need.

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[jaambor]

Thanks for your solution, I will test it out tomorrow and see how effective it is.  It looks like it will work great.  

    Jeremy

[bcdobbs]

Hi Jeremy,

I think I would take table into PowerQuery (Transform Data).

Selected your Cust ID column and click Unpivot other columns.

That would then let you look up any of your ids from a vendor id.

You'd end up with a table like:

As long as those with only one vendor id were left as blank (null) values power query will deal with it.