Creating lookthrough from 1 Fact table to another Fact table

[Johnsnowlife]

I have 2 Fact Tables and I want to be able to create a pie chart with a hierarchy using the info from both tables.

Fact Table 1: Is a Client Table showing the investment products the client owns. 

Fact Table 2: Is a Fund Holdings table showing the instruments in the investment products. 

PBIX File Link

I can currently create a hierarchy of Client/Investment products and of Fund/Instrument. But I want to create one of Client/InvestmentProduct/Instrument so that I can show the combination of the same instrument in different investment products. 

[v-yuezhe-msft]

@Johnsnowlife,

 Do mask sensitive data when uploading sample PBIX file.

In your scenario, you don’t have to create hierarchy. You can directly drag these fields(Client Name, Fund Name and Instr) to pie chart and use the drill down option to drill down the visual.

Regards,
Lydia

[Johnsnowlife]

Oh! I see that works. But now the values that need to be plotted are different for each sub-level. 

If I drill into Client Name, the pie should show me the market value of the client's holding in the fund. And if I drill into the fund it should show me the fund's holding of the instrument. 

And if I expand all down to the lowest level of the hierarchy it should show me the weighted percentage held of each instrument. 

ie. Sum [(weight of fund in client's portfolio)*(weight of instrument in fund)] over all instruments in fund and all funds in client portfolio. 

[v-yuezhe-msft]

@Johnsnowlife,

Could you please post expected result in table based on the sample data in your PBIX file?

Regards,
Lydia

[Johnsnowlife]

The results tab on this excel sheet shows the desired result

[v-yuezhe-msft]

@Johnsnowlife,

In the Pie chart, when you drag Market Value to Values if the chart, each time you drill down to a different hierarchy level, the chart will show you market value of the current hierarchy level, in this case, it is not possible to show you weighted percentage held value in  the lowest level of the hierarchy.

If you want to show weighted percentage held value, you can drag the weighted percentage held measure/column to Tooltips of the pie chart. This way, when you drill down to each hierarchy level, pie chart will show  you both market value and show weighted percentage held value after you hover mouse over the chart.

 
Regards,
Lydia

[Johnsnowlife]

Ok, that makes sense. But if I make the values on the pie chart equal the Lookthrough instrument Holding = (% of fund in Client portfolio) * (% of instrument in Fund) then both levels will be correct. 

I'm not sure how I get the relationships to work so that I can still use 2 fact tables. Can you suggest the DAX to get the formula above? 

[v-yuezhe-msft]

@Johnsnowlife,

Could you please explain that which table the date field in Results Sheet of Excel file comes from? I note that it only shows date 2/29/2013.

Regards,
Lydia

[Johnsnowlife]

I only entered the date 2/29/2013 in the excel cell to make it faster to explain. But in PBI I would have selected one of the dates in a slicer. 

[v-yuezhe-msft]

@Johnsnowlife,

Which table do you drag the date field to create slicer and expected table visual?

Regards,
Lydia

[Johnsnowlife]

From my Calendar Table which is a proper time intelligence table. 

[v-yuezhe-msft · Accepted solution]

@Johnsnowlife,

Please review this modified PBIX file.

Regards,
Lydia

[Anonymous]

@Johnsnowlife  have you got this working , if so could you please share the logic or .pbix file . am unable to extract the file from onedriver , i guess its been removed (since this conversation was a long time ago) . Thanks