Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NLC
Frequent Visitor

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 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. 

1 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] ) )


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

4 REPLIES 4

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] ) )


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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. 

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/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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 18The graph I am trying to make, the value for the last day should be around 18Sample of my historical price tableSample of my historical price tableSample of my Portfolio dataSample of my Portfolio dataMy current relationship set up between tablesMy current relationship set up between tables

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.