locked
Complex measure in PowerPivot 2016 - pass through definition Table RRS feed

  • Question

  • In Excel 2016, I have the following data model:

    link to sample workbook: https://www.dropbox.com/s/v4gk274xl3tuxww/PNL%20-%20NEW%20-%20Sample.xlsm?dl=0

    2 data tables (newPNLData and HistoData) both linked to an instrument definition (through field conid).

    NewPNLData contains buy/sell rows for instruments

    HistoData contains the historical price of these intruments

    MarketData contains the static unique definition of each instrument.

    I would like to create a measure adding the quantity (in newpnldata) per instrument (conid), times the multiplier of each instrument (in MarketData), times the LIVEprice (in HistoData). I would then slice HistoData to get the value of the inventory at a certain timeStamp.

    name of the currently working measure = "LIVE PNL$", replacing the LIVE in MarketData by LIVE in HistoData ...

    I cannot make it work. Is it possible with my data model ? am I missing something ?

    Thank you in advance


    • Edited by syx2017 Tuesday, September 26, 2017 11:13 AM
    Monday, September 25, 2017 7:42 PM

Answers

  • Hi syx2017,

    Thanks for your question.

    According to the diagram view in your model,  the relationships for these 3 tables are below:
    newpnldata Many:1 MarketData 1:many  HistoData, see below image:

    You can get the quantity (in newpnldata) per instrument (conid), and times the multiplier of each instrument (in MarketData). But you can not times the LIVEprice (in HistoData), as there are many LIVEprice (in HistoData) per instrument (conid in MarketData).


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by syx2017 Thursday, September 28, 2017 11:43 AM
    Thursday, September 28, 2017 7:37 AM

All replies

  • Hi syx2017,

    Thanks for your question.

    To resolve your question more efficiently, please share the sample data and the expected results. If you can share the link where we can download the workbook from, that would be much better.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 26, 2017 2:39 AM
  • I added a sample workbook and a bit more details. Thanks
    Tuesday, September 26, 2017 11:14 AM
  • Hi syx2017,

    Thanks for your question.

    According to the diagram view in your model,  the relationships for these 3 tables are below:
    newpnldata Many:1 MarketData 1:many  HistoData, see below image:

    You can get the quantity (in newpnldata) per instrument (conid), and times the multiplier of each instrument (in MarketData). But you can not times the LIVEprice (in HistoData), as there are many LIVEprice (in HistoData) per instrument (conid in MarketData).


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by syx2017 Thursday, September 28, 2017 11:43 AM
    Thursday, September 28, 2017 7:37 AM
  • thank you very much. Any suggestion as to how to restructure the datamodel to be able to achieve this goal ?

    Best

    Thursday, September 28, 2017 11:44 AM