Forum Discussion

NLC's avatar
NLC
Frequent Visitor
9 years ago
Solved

DAX SUMX REFERENCE ANOTHER TABLE

Hi everyone, So I am making a portfolio report in power Bi. I am currently trying to get an average return for an industry and display it in a line chart, with the date on the x axis, and return o...
Need Help
  • MattAllington's avatar
    MattAllington
    9 years ago

    Your original formula wont work because you are missing a calculate to force context transition.  Check out my evaluation contexts video here https://exceleratorbi.com.au/my-mdis-presentations/

     

     

    industryreturn =
SUMX (
    'Portfolio',
    CALCULATE (
        AVERAGE ( 'Historical Prices'[Close] ) * ( 'Portfolio'[Shares] )
            / ( 'Portfolio'[Cost basis] )
            - 1
    )
        * 'Portfolio'[Shares]
)
    / CALCULATE ( SUM ( 'Portfolio'[Shares] ) )
NLC's avatar
NLC
Frequent Visitor
9 years ago

Hi Matt, Thank you for taking time out of your day to help me with my problem! The link you provided in your article was very insightful, although I am still confused how referencing another table works in a sumx function.  Here is some sample data, (please let me know if you want the sample data in another format.)

The graph I am trying to make, the value for the last day should be around 18Sample of my historical price tableSample of my Portfolio dataMy current relationship set up between tables