›

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

Target-Actual on Allocation

jwengajwenga
in Excel Workbook
On the Dashboard tab, under the "Allocation Target", my pivot table does not sum up the total of the "Target - Actual". I think this # would provide valuable info in the total amount your portfolio is out of "allocation". Correct? if so, how would I get that field to auto sum? I apologize in advance as I am pretty good with excel general but weak on pivot Tables for the moment.

Comments

  • VidasMatelisVidasMatelis
    Hi,

    Target - Actual at all level is always 0.
    Lets say you 100k invested and have following target:

    USA: 20%
    Canada: 80%

    If your actuals are:
    USA: 30%
    Canada 70%

    Then Target - Actual would be:
    USA: 20%-30% = -10%
    Canada: 80% - 70% = +10%
    Total -10% + 10% = 0%
    I showed above in %, but same results if you would use amounts.

    Probably you are looking for a sum of values > 0, as that would give you idea of how much money you should "move". Are you comfortable adding DAX measures? If so, I can post here calculation that you should add, but for that let me know your Excel version.

  • jwengajwenga
    I'm using Excel 2016...
  • jwengajwenga
    Yes, sum of values that would be the amount that I would nee to move to reallocate.
  • VidasMatelisVidasMatelis
    In Excel 2016 (assuming you have PowerPivot full support):
    1. Go to PowerPivot menu -> Measures button -> Manage Measures.
    2. Select measure "Allocation Target - Actual", click Edit.
    3. Replace current formula:

    =ROUND(Report[Allocation Target Value] - Report[Total Value], 2)

    with new formula: 
    =IF(HASONEVALUE(Allocation[Allocation])
, ROUND(Report[Allocation Target Value] - Report[Total Value], 2)
, SUMX(Allocation, IF(Report[Allocation Target Value] > Report[Total Value], ROUND(Report[Allocation Target Value] - Report[Total Value], 2)))
)
    4. Click "OK", then "Close".

    If you do not have full PowerPivot support (no PowerPivot menu), then you cannot change that formula, sorry.
  • jwengajwenga
    For some reason that changes my entire dashboard and does not accomplish what I was hoping for. See screen shot of output after inserting that code.
  • VidasMatelisVidasMatelis
    You simply moved right in the dashboard. Select any cell and move left. Because column headers are hidden, it is confusing. So after that, are results ok?
  • jwengajwenga
    yes they are, sorry i should have realized that...thanks
  • VidasMatelisVidasMatelis
    No Problems. PS is developed on Excel 2010 and then for release is upgraded to 2013 and 2016. In 2013 you can already build charts without pivots. But in 2010 you must build pivot then based on that pivot you can build chart. So reports you see on the right side - are reports for charts.
Sign In or Register to comment.