DAX SUMX REFERENCE ANOTHER TABLE

[NLC]

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 on the y-axis. There are several stocks in each industry. For example, there are 2 stocks for consumers staples. The way my data is formatted there are two tables, one that has the historical closing price of a stock (Historical price), with each row representing the values of one day, of one stock. The other (portfolio) has the cost basis, industry, and other general information about the stock, with each row representing one stock. The tables have a relationship through the ticker column, with the one side being the portfolio table, and the many being the historical price table.

So this is what I have written so far although it is giving me the wrong value;

industryreturn =
SUMX (
'Portfolio',
(
AVERAGE ( 'Historical Prices'[Close] ) * ( 'Portfolio'[Shares] )
/ ( 'Portfolio'[Cost basis] )
- 1
)
* 'Portfolio'[Shares]
)
/ SUM ( 'Portfolio'[Shares] )

The problem I think is that the AVERAGE ( 'Historical Prices'[Close] )  is giving the average of the closing for all stock prices, instead of the average of just the one stock that is referenced in the portfolio row with SUMX. If anyone could help me out with this would be greatly appreciated. 

[MattAllington · Accepted solution]

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]

Thank you, that formula solved my problem, I guess I still need to brush up my knowledge on the difference between row context and filter context. 

[MattAllington]

It would be easier to help if you could provide some sample data. But take a look at my article here and it may help you. https://exceleratorbi.com.au/use-sum-vs-sumx/

[NLC]

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