PowerPivot Help

jgedwardsv

New Member
Joined
Oct 27, 2016
Messages
31
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
 
Well I hope so :). The key to self service BI is having the skills to write he DAX and the business knowledge in the one brain. The more you learn, the easier it becomes of course. Good luck.
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Matt -

I have another question about calculated columns.

With the data sample that I uploaded on the participant table, there are several columns that I calculate in excel. Would it be possible to handle those calculations in powerpivot to remove the ability for someone to change my formulas? For instance, I know how to calculate the age, but ran into some issues with calculating the ss benefit estimate. I'm guessing this is because I am using the sumproduct formula which isn't available in powerpivot. Also, I wasn't able to find a fv formula alternative either which is necessary to calculate the future account value.

In a sense, what I would like to do is remove anything from the data model that someone (other than me) should have the ability to change in excel.

John
 
Upvote 0
Technically anyone that knows what they are doing can change any formulas. If you are importing it from Excel (separate workbook) and you do the precalc in Excel, then they can't change it if they can't see it. So in a sense doing it in Excel may be more secure.

However even if you do it in Excel, anyone can still change it in Power Pivot. E.g. If you have following measure running of a column you calculated in Excel
commission = sum(table[payments])

then anyone could change the formula to something like this.
commission = if(hasonevalue(users[name]),if(values(users[name])="Matt Allington",sum(table[payments])*1.5,sum(table[payments])))
which basically checks the user and pays me 50% more than it should. (Note the dax may not be perfect, but the point is that it could be done. )

regarding sumproduct, sumx is very similar (If you write it as a measure). But there is so much to learn and I still don't really understand your data. My book has a section on sumx and you can read this article on my blog When to use SUM vs SUMX in DAX
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top