• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining tables inpower pivot

K

karthikesh

New Member
Hi,
I am trying to create a personal investment dashboard. I would like to summarize for:

1. Investment by type (equity, debt, cash), region (US, EU, UK etc), industries (IT, Financial, Health care)
2. Investment across institutions (A, B, C)
3. Investment across investors (Erika, John etc)

I have 3 tables:

Table 1: MF_Master --> Multual fund master data where all the fund names along with their allocations are included -> master table
Table 2: MF_Invested --> 3 column stating fund name, invested value and current value
Table 3: Stocks_Invested --> Stock trading where data type is stocks, with info on the investor, institution, and region and sector

Tables 2 will be updated monthly manually, and table 3 will be updated daily, and entries might change based on the transactions, e.g. sell MSFT buy APPL etc.

Now I am trying to combine this in power pivot to get the above metrics.


1. I can use table 1 and table 2, and get calculated columns and filters for the MF section.
2. I am trying to combine and get a total sum of equities or sum invested with one institution etc. When I create connections they dont work so I am doing something wrong.

I am also getting errors in the connections with the stocks_invested table.

Request your help.

Thanks, Karthikesh
 

Attachments

  • Test_excel.xlsx
    834.3 KB · Views: 4
Hmm, your data does not respect the golden rules of data modelling.
You should have normalized lookup besides factual tables.

At first glance you only have fact tables. To link them, you need to look at the common columns, get the unique value and put those in a new lookup table. Then link the fact tables to this lookup table.
 
Thank you G. I am using the tables I had to monitor the investments to build the pivots. Now I am looking for a consolidated dashboard. I am not sure I understand how a look-up table for this would be constructed.
 
Hi
I have now added lookup tables and a key to the fact tables. I have also established relations with them, but I am missing something here since the pivots are not as expected.

Perhaps the problems are with the two fact tables MF_Master and Stocks_Master - which allocates an investment along types (equity, debt, cash), regions and industries.

I have attached the new excel sheet.

Looking for advice on the data model and the mistake I might be doing here.

Karthikesh
 
Hi all,
I am trying to add the file but I am not able to do so. The file gets loaded and then it gets struck off.
 
Hi,
I'm a bit short on time to really go through this in details.
You can combine two facts tables. Also here you need to have some understanding on how DAX is working. Otherwise indeed you can end up with strange looking pivots filled with cartesian products in the rows/columns and repeated total values that don't make sense.
Can't promise much, but I'll try to have a closer look later. Surely "later" is not this week. Kind of busy at work with a data model challenge myself.
 
You must log in or register to reply here.
Back
Top