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] ) )
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
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

    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.