Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

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

  1. #11
    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

    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.
    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?

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

    Default Re: PowerPivot Help

    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

  3. #13
    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

    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
    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?

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
  •