none
Many Calculated Columns

    Question

  • Hi, I have a relatively small amount of data (currently 178 rows and 98 columns of data), but a large quantity of calculated columns are needed in addition.  Currently we have split out the data into 14 different data tabs, each having 5-8 tabs attached that show the data in different formats.  It's crazy, I know, but I didnt' set it up.  I'm trying to clean it up and was thinking that PowerPivot might be the best answer.  There are going to be a ton of calculated columns and I wondered if anyone had any advice for me. 

    The data is made "pretty" and links to a PowerPoint file with tables and charts.

    Thank you.




    • Edited by Nalani Tuesday, October 22, 2013 7:47 PM
    Tuesday, October 22, 2013 7:42 PM

Answers

  • Hello Nalani:

    On a recent project, a client was able to replace a very time consuming process of preparing multiple lists on 20 separate worksheets with the use of Power Pivot. Preparing reports using the original workbook required hours of cutting and pasting data, adding VLOOKUPS and checking to make sure that formulas were correct and data was not lost.

    Power Pivot allowed this user to treat the data more like a database. In this case it was survey data...so all the survey data could be pasted to a table on a single worksheet. This table contained a few calculated columns in the right-most columns and these formulas automatically replicated on new rows of data that were pasted to the table.

    The table of survey data was then linked into the Power Pivot data model along with several lookup tables like Customer and Region. The relationships in the Power Pivot data model linked the survey data to the lookup tables.

    Pivot table and charts were then used to present the data. A few calculated fields using DAX formulas were used to enhance these pivot tables and pivot charts. 

    End result: a streamlined process, much quicker to use and less prone to errors. Each quarter new survey data is pasted to the table, customer and other lookup tables are updated, the data model is refreshed and pivot tables and charts are automatically updated.

    Challenges:

    1) Power Pivot forces you to think in database terms. For Excel users who have not used a database program (like Access) this can involve learning new concepts.

    2) To get the final 10% of your requirements met using pivot tables and pivot charts you may need to create some calculated fields using DAX. The trick is to learn just enough DAX to meet your requirements without becoming lost in the many features of the DAX language.

    3) Automatically producing multiple PDF reports or PowerPoint slides from many pivot tables and charts involves a bit of VBA code. You may not want to distribute your master workbook with the data model to others and copying pictures of charts and tables to PowerPoint or to another Excel workbook can be time consuming without an automated process. 

    Good luck with your project!

    John


    John Lacher john@pivotdashbooard.com

    Tuesday, October 29, 2013 10:36 AM

All replies

  • Any ideas for Nalani?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, October 25, 2013 7:54 PM
    Owner
  • Hello Nalani:

    On a recent project, a client was able to replace a very time consuming process of preparing multiple lists on 20 separate worksheets with the use of Power Pivot. Preparing reports using the original workbook required hours of cutting and pasting data, adding VLOOKUPS and checking to make sure that formulas were correct and data was not lost.

    Power Pivot allowed this user to treat the data more like a database. In this case it was survey data...so all the survey data could be pasted to a table on a single worksheet. This table contained a few calculated columns in the right-most columns and these formulas automatically replicated on new rows of data that were pasted to the table.

    The table of survey data was then linked into the Power Pivot data model along with several lookup tables like Customer and Region. The relationships in the Power Pivot data model linked the survey data to the lookup tables.

    Pivot table and charts were then used to present the data. A few calculated fields using DAX formulas were used to enhance these pivot tables and pivot charts. 

    End result: a streamlined process, much quicker to use and less prone to errors. Each quarter new survey data is pasted to the table, customer and other lookup tables are updated, the data model is refreshed and pivot tables and charts are automatically updated.

    Challenges:

    1) Power Pivot forces you to think in database terms. For Excel users who have not used a database program (like Access) this can involve learning new concepts.

    2) To get the final 10% of your requirements met using pivot tables and pivot charts you may need to create some calculated fields using DAX. The trick is to learn just enough DAX to meet your requirements without becoming lost in the many features of the DAX language.

    3) Automatically producing multiple PDF reports or PowerPoint slides from many pivot tables and charts involves a bit of VBA code. You may not want to distribute your master workbook with the data model to others and copying pictures of charts and tables to PowerPoint or to another Excel workbook can be time consuming without an automated process. 

    Good luck with your project!

    John


    John Lacher john@pivotdashbooard.com

    Tuesday, October 29, 2013 10:36 AM