›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

% of Overall Account Profit by Investment

jrwsteelersjrwsteelers
in Suggestions
I would like to be able to calculate the % each individual investment contributes to the overall Gain/Loss of the account for a given time period. For example if your overall account shows a profit of 5% for the year, I would like to be able to see how each holding contributes to that 5%. The formula would just be something like (Profit of individual holding)/(Total profit of account). Maybe with an adjustment included in the formula so for years with an overall loss, gains in individual holding don't appear as negative %'s. I'm not sure how to set that up in the PowerPivot Report.

Any help would be appreciated.

Thanks,
Joe

Comments

  • jrwsteelersjrwsteelers
    I ended up just creating another linked table that references the total profit in the pivot table on the holdings tab. I then added a formula to the report tab in powerpivot along the lines of "% of Overall Profit:= Report[Profit]/Abs(Sum(Newtable[Overall Profit]))". I'm sure there is a prettier way of doing this but this works. I just have to refresh all of the connections when I change the Holdings Pivot Table, because the "% of Overall Profit" values in the pivot table depend on the "Overall Profit" value in the pivot table itself.

    Thanks
  • VidasMatelisVidasMatelis
    Hi Joe,

    DAX formula for such calculation would be something like:

    =DIVIDE(Report[Profit], CALCULATE(Report[Profit], All(Symbol)))

    Above formula would work when you filter by year, allocation, account, etc.

    Can I also suggest that you look at Profit reports in "Portfolio Slicer for Power BI". There is waterfall chart there that shows how each symbol contribute to total unrealized capital gain. You can easily create such report that shows how each symbol contributes to profit.
  • jrwsteelersjrwsteelers
    Thanks for formula help. I also hadn't looked at the reports in Power BI. Those are very helpful.
Sign In or Register to comment.