Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: PowerPivot Help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PowerPivot Help

    Hi All,

    I am trying to create a model to evaluate participant data and then create a series of charts and tables to correlate that data output to a specific target date mutual fund series.

    I have the logic thought out, but am having trouble executing this with powerpivot.

    I have 3 tables and a helper age table built. One table contains all the raw data on each target date series with values of equity exposure as the columns (i.e. 50 years to date x%, 45 years to date y% etc etc). A second table contains the participant data calculated in excel (I calculate future account balance and estimate social security payments to derive a total benefit at retirement and then calculate a replacement ratio based on current income and then based on inflated income in the future). My final table contains an array of replacement ratios, grades for each, and corresponding equity allocation.

    I am having trouble calculating a participant's age group (in columns in the equity allocation) and corresponding that to a participant's income replacement ratio (the rows in the same table). How can I pull the age group and replacement ratio to find the equity allocation associated with it? I believe this would be an index/match function in excel, but given that this analysis is intended to be run repeatedly with varying sets of participant data (i have linked the table from an external excel workbook to prevent someone from messing with the data model).

    Thanks for your help, I'm really stuck here and believe I am close to figuring out how to model this correctly.

    John

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    Hi John, I would like to help an der I see you have thought this through. This is not my industry and I don't really understand the scenario. Any chance you can post a sampl eworkbook that illustrates the problem?
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    New Member
    Join Date
    Oct 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    Matt,

    Thanks for the offer and sorry for the delay (I was away this weekend with the wife and didn't get back until Monday night).

    What is the best way to get the files to you? The way I have it set up now is with the data split between multiple linked workbooks, all linked to the analysis workbook. Would I need to send them all? My intent was to have one workbook that contained only the data from the participants so anyone in the firm can update the data and not have the chance of messing with the data inputs for the funds we are analyzing or the age table, etc. I want to prevent editing of things that shouldn't be edited as much as possible.

    John

  4. #4
    New Member
    Join Date
    Oct 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    We also have a sharepoint system set up and my end goal was to have participant information stored in each company site in a sharepoint list that I can then pull from for the analysis and build as many calculations into the powerpivot as possible.

  5. #5
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    We should do this via the forum. The best way to get help is to provide a sample workbook that has a small amount of test data that demonstrates the problem, then post it a link to it stored on Dropbox or similar
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  6. #6
    New Member
    Join Date
    Oct 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    Matt,

    Please see below for the raw data I am looking to manipulate:
    https://www.dropbox.com/sh/z8nx1cayf...0tweSozia?dl=0

    I copied the tables from the existing workbooks and put them all into one file so it would be more concise and you can hopefully see the logic.

    I have data for participants where I have calculated out future balances and withdrawal amounts to get to the end goal of an income replacement ratio.

    What I want to do is measure each participant based on their age group and the income replacement ratio to determine what percentage of equity exposure each one should have, separate them into their respective 5 year increments and aggregate the data to compare to the equity allocations in the TDFData chart. I organized the chart based on years to retirement so the information (in theory) shouldn't need to be updated frequently unless the equity allocations change for an individual target date fund series.

    The end goal is to determine which series most closely matches the equity allocations that are most appropriate for the overall participant population (up to 5 "closest" matches would be great, allowing for additional comparison using our existing tools to measure performance, holdings, costs, etc.).

    My only issue is that this is not a one time thing - this is something that I want to be able to repeat based on the participant data inputs changing, both for this existing suite of participants and other groups - otherwise I could manually do all these calculations in excel, creating tables for each individual age group. In my original iteration I used many different tables and the spreadsheet became so large that it was unwieldy.

    Thanks again for your help and please let me know if I didn't explain something properly.

  7. #7
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    Unfortunately I don't understand the data or the industry, so that makes it hard. Self Service BI is great because you know the industry and the tools, but of course that doesn't help when you are learning. To try to move this forward, let me ask some questions and ask for more info

    Quote Originally Posted by jgedwardsv View Post

    What I want to do is measure each participant based on their age group and the income replacement ratio to determine what percentage of equity exposure each one should have
    What does "measure" mean in this instance?

    separate them into their respective 5 year increments and aggregate the data to compare to the equity allocations in the TDFData chart.
    Again, I don't know what this means "separate into their respective 5 year increments"

    Maybe a worked through example using Excel would help
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  8. #8
    New Member
    Join Date
    Oct 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    Matt,

    Sorry for the confusion - I'm probably not describing this correctly.

    For the first question, what I am looking to do (and have done through powerpivot) is use the age table to create groups of employees based on their age - calculate out each employee's age and then drop them into a group based on years to retirement (starting with 50, declining by 5 year increments to -20 years to retirement). This is because the target date funds come in 5 year incremental steps (i.e. 50 years to retirement is the 2060 fund).

    Would it make sense to make this a calculated column in powerpivot or is there a way to write a formula in DAX that would do this automatically when dropped into the pivot table?

    The reason I want to separate the employees into 5 year increments is to match each employee up to the respective fund that they should be in based on their age, then aggregate the replacement ratios for each of those employees. For instance if I have 3 employees that would fall in the 50 years to retirement, find the average replacement ratio, tie it to the appropriate equity allocation to determine the best fit fund in the target date data workbook.

    What I am trying to achieve is an overall best fit target date fund series for the total employee group, based on the lowest differential in total equity for each 5 year block of employees. It would ideally be visualized by a trend line that most closely corresponds to the declining equity allocations of each suite of target date funds.

  9. #9
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    Quote Originally Posted by jgedwardsv View Post
    For the first question, what I am looking to do (and have done through powerpivot) is use the age table to create groups of employees based on their age - calculate out each employee's age and then drop them into a group based on years to retirement (starting with 50, declining by 5 year increments to -20 years to retirement). This is because the target date funds come in 5 year incremental steps (i.e. 50 years to retirement is the 2060 fund).
    OK, got it. Read my article about age banding here Banding in DAX - Excelerator BI

    ..then aggregate the replacement ratios for each of those employees. For instance if I have 3 employees that would fall in the 50 years to retirement, find the average replacement ratio, tie it to the appropriate equity allocation to determine the best fit fund in the target date data workbook.
    Lost me again. But with the bands above, does that help you?
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  10. #10
    New Member
    Join Date
    Oct 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot Help

    I think I'm going to have to put together several different bands to make this work... This will be my project for the very slow Christmas holiday week.

    On another note - I just ordered your book from Amazon, I'm going to guess that I will find some information I need in there as this project progresses. Thanks so much for your help so far, I'm sure I'll be back on here looking for more help once I solve the banding issue.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •