Page 1 of 1

To buid a table with distinct columns fron two tables

Posted: Tue Nov 20, 2018 1:35 pm
by carlos barberis
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]
Thankls a lot in advance

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

Posted: Wed Nov 21, 2018 9:34 am
by RamanaV
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
by carlos barberis
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
the name of data base is "AdventureWorks" and you can download it from:
http//xbi.com.au/learndax
Thanks a lot in advance for your help

Re: To buid a new table

Posted: Sun Nov 25, 2018 6:03 am
by carlos barberis
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
by RamanaV
Hi,

You can do it in Power Query.
Follow these steps.

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