PowerPivot Help

[jgedwardsv]

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

 

[Matt Allington]

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.

 

[jgedwardsv]

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

 

[Matt Allington]

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