Forum Discussion

Johnsnowlife's avatar
Johnsnowlife
Helper III
8 years ago
Solved

Creating lookthrough from 1 Fact table to another Fact table

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.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Johnsnowlife,

    Please review this modified PBIX file.

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Johnsnowlife
    Helper III
    8 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Johnsnowlife,

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

      Regards,
      Lydia

      • Johnsnowlife's avatar
        Johnsnowlife
        Helper III
        8 years ago

        The results tab on this excel sheet shows the desired result