Page 1 of 1

### To buid a table with distinct columns fron two tables

Posted: Tue Nov 20, 2018 1:35 pm
Hi Friends,
I would appreciate your help to build a table with total sales for 4 disctinct colums from two different tables (Products and customers). For instance the following table:

Categories Class Gender MaritalStatus [Total Sales]

So, If There are 4 Categories, 4 Class, 2 MaritalStatus and 2 Genders, then total number of rows would be 4x4x2x2 = 64 Rows (max) for [Total Sales]

### Re: To buid a table with distinct columns fron two tables

Posted: Wed Nov 21, 2018 9:34 am
Hi,

From where the [Total Sales] coming from? Do you have a Sales table with SalesAmount column? and is [Total Sales] a measure with the formula as SUM(Sales[SalesAmount])?

I am assuming that you have the above information. Otherwise you need to have that. Sales table is your Data table.

You have 2 Lookup tables - Products and Categories. Good. But how will you connect them to your Sales table.

For each sales transaction, how will you know which product is sold and to which customer?

Unless you give this information, it is difficult to help.
Actually, a workbook with sample data would help for better understanding.

Best wishes

### Re: To buid a table with distinct columns fron two tables

Posted: Thu Nov 22, 2018 3:05 pm
Hi RamanaV

Im using Database example of Matt's Book "Supercharge Power BI"
[Total Sales] = SUM(Sales[ExtendedAmount] is a measure in "Sales" data table
Customers and Products are lookup tables
http//xbi.com.au/learndax

### Re: To buid a new table

Posted: Sun Nov 25, 2018 6:03 am
Hi Ramanav,

To be much simpler, suppose you have 2 Look-Up tables and 1 Data table, all conected. The first Look-Up table has 3 columns A, B, C; the second Look-Up table has 3 columns D,E and F; and the Data Table has 3 columns A, D, G,(where G is "Sales" column).
What I need is to create another new table with columns B, E, G
Thanks a lot for your help
Carlos Barberis

### Re: To buid a table with distinct columns fron two tables

Posted: Sun Nov 25, 2018 3:27 pm
Hi,

You can do it in Power Query.

1. Merge the tables Products and Sales using ProductKey column. Use Merge Tables as New option.
It creates a new query - Merge1.
2. In Merge1, expand the Table column (column on the extreme right), keeping only CustomerKey and ExtendedAmount columns.
3. Remove all columns except for Category, Class, CustomerKey and ExtendedAmount.
4. Select all columns and remove duplicates.
4. Merge with table Customers using CustomerKey column.
5. Expand the Table column (column on the extreme right), keeping only Gender and Marital Status columns.
6. Remove CustomerKey column.
Now you are left with only the required columns.
Remove duplicates.
And reorder the columns the way you want.

Best wishes
Ramana