›

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

Addition of Portfolio Stock % to Holdings Tab

dstirlingdstirling
in Suggestions
Would it be possible for future versions to have a column in the holdings tab which would show what the percentage is of each stock in the account/portfolio accounts for?

Thank you :)

Comments

  • VidasMatelisVidasMatelis
    Thanks for suggestion.
    Will be done - measure name "Portfolio %". You can add this measure yourself for now using following formula:
    =DIVIDE(Report[Total Value], CALCULATE(Report[Total Value], ALL(Symbol)))
    Note: This percentage is calculated just against Symbol table. That is - if you will select slicer Account value, "Portfolio %" will show percentage in that selection.
  • dstirlingdstirling
    Thank you for the prompt reply!

    Could you tell me where exactly I would add the formula you suggested.

    Thank you.
  • VidasMatelisVidasMatelis
    edited May 2017
    Hi,

    How to add new formula depends on Excel version (or different steps for PowerBI). What Excel version are you using?
  • dstirlingdstirling
    I'm using Excel 2016
  • VidasMatelisVidasMatelis
    I am assuming that you have "Full" PowerPivot support - that is you have PowerPivot menu in Excel. If you do not, then you cannot add this measure yourself.
    In Excel 2016:
    1. Go to Holdings tab. Select any cell inside holding pivot table.
    2. Choose PowerPivot menu, select arrow down below "Measures" button and choose "New Measure".
    3. TableName : choose Report.
    4. Measure name: enter Portfolio %
    5. Formula - enter formula above
    6. For formatting choose "Number" and then "Percentage"
    7. Click OK.

  • dstirlingdstirling
    Hi Vidas

    I followed your instructions but it doesn't want to seem to populate to the report. I've included screen shots...

    Any suggestions?
    P2.PNG 11.3K
    P1.PNG 60.1K
  • VidasMatelisVidasMatelis
    Hi,

    Sorry I do not see P2.png screenshot - I do not see if value is 0 or if some value is calculated?
  • dstirlingdstirling
    How is this...
    P3.PNG 88.2K
  • VidasMatelisVidasMatelis
    OK,

    Sorry - I think one more step is missing in my description.
    select any cell in that pivot, then on the right you should see list of fields. If you do not see list of fields, then right mouse click and choose "Show Field List". From that field list expand table "Report" and check "Portfolio %" new measure.
  • VidasMatelisVidasMatelis
    And just to add - if you select just one "Account" then this value will be for one account. If you want to show portfolio % independent of account selection, you should change formula to:
    =DIVIDE(Report[Total Value], CALCULATE(Report[Total Value], ALL(Symbol), ALL(Account)))
  • dstirlingdstirling
    Hi Vidas

    That extra step did the trick! I preferred it being for the account selection - so the first formula you provided.

    Thanks so much!
  • timgatimga
    Great piece of info added here. Thanks for the suggestion and procedures on how to add the field.
Sign In or Register to comment.