How to join 2 tables with different columns to match (and within each different infos)

[Alex_192]

Hello everyone,

I am quite new on PowerBi but despite some hours of youtube tutoriel and reading on the net, I can't manage to join my two tables in Power BI..

I would really appreciate if someone could help me 🙂

Let me explain:

I have a 1st table (invoice database) with invoice where I have 1 line = 1 invoice with:

- salesman name (A, B, C, D, E, G) - here the salesman "G" is not in the budget table

- product name (Pdt1, Pdt2, Pdt3, Pdt4, Pdt 6) - here the Pdt 6 is not in the budget table

- sales (in value)

- quarter

My second table with the budget where I have 1 line = 1 quarterly budget per product per salesman with:

- salesman (A, B, C, F) - here the "F" salesman is not in my invoice database

- product name (Pdt1, Pdt2, Pdt3, Pdt4, Pdt 5) - here the Pdt 5 is not in the invoice database

- quarter

- Budget (in value)

How do I join those 2 tables to get:

per salesman (A, B, C, D, E, F and G) and per product name (Pdt1, Pdt 2, Pdt 3, Pdt 4, Pdt5 and Pdt 6) the sales realized and budget per quarter ?

(I can't manage to have the full list of salesman + the full list of product name with the correct allocation of both sales & budget)

Thank you very much for you help!

Alex

[spuder]

Hi @Alex_192

as far as I can see you have two fact tables with different values.

What you need are two more dimension tables.

The first one is a table with all salesmen and the second one with all products.

Connect BOTH with BOTH fact tables salesmen <-> salesmen and product <-> product

You should now be able to use salesmen from your dimension table as your category for visuals and the same with products.

[Alex_192]

Hello Spuder,

Thanks for your prompt answer !

How would you connect those dimension table with the "invoice" and "budget" table, I probalby misunderstood something as I am creating a loop (one with salesmen one with product) between invoince and budget table

Thanks

Alex

[spuder]

At the beginning just create two tables.

DimSalesmen

[Sales Member]

Salesman a

Salesman b

Salesman c

...

DimProducts

[ProductName]

Product a

Product b

Product c

...

Both tables only have one column.

Now create a relationship between The Column [ProductName] in DimProducts and the Product Columns in the existing fact tables. Do the same with Salesmen.

Now create a matrix visual and set [ProductName] column to Rows and [Sales Member] Column to rows, too.

Voila 

[Alex_192 · Accepted solution]

Perfect thank you it works!