locked
Converting from Excel to PowerPivot Model - Formula Issues RRS feed

  • Question

  • Greetings!

    I'm attempting to move my work to PowerPivot for efficiency, but have to be able to duplicate prior calculations for pivot reports. Below is one of my old excel formulas, but first the setup.

    There is a (non)unique identifier named "Document Number" (it is duplicated to show accounting transactions and is in all of my tables, so can't do an easy one-to many relationship). In Excel, I would create the following formula to pull the respective amounts from all other tables into Table1:

    =SUMIFS(Table2[[#All],[Amount]],Table2[[#All],[Document Number]],Table1[@Document Number])/COUNTIFS(Table1[[#All],[Document Number]],Table1[@Document Number])

    Once I pull in the amounts into Table1, I then add a helper column to identify when amounts across the systems are not equal, thus giving me a filter once pivoted. The model works great to show me actionable information and reporting, but is painfully slow at this point (up to 45 min to calculate each column, with 3-4 tables all having >100k records which I have to pull into the one table in the same manner). I am reading related books and reading posts about efficiencies, but this one just is what it is, so I'm taking it to PowerPivot! Side note: PowerPivot doesn't like to pivot 160K document Numbers by month with available resources (neither do I), so I definitely need that filter just to show the issues.

    Computer: HP Envy, i7 5500, 12 GB RAM - 64 bit Office 2013 - 32 bit Excel 2013 (as installed)

    Thanks for any help!!

    Sunday, March 29, 2015 7:12 PM

Answers

  • Update!

    I watched a few videos and figured out a solution (calculated column in Table1):

    =Calculate([Table2 Total Cost],Table1[DocNumber])/[Count of Table1 DocNumber]

    What was 45 minutes took about 5 seconds!! AMAZING!!!

    I am running into a second issue now :( I'm trying to duplicate this EXACT calculated column in Table1 with Table3, but it's kicking circular reference error. I turned on allow circular references, in Excel, but didn't fix the issue. I recreated a calculated measure with a different name (Count of Table1 DocNumber2), but no dice.

    Going to keep trying and reading, but any suggestions are appreciated. Thanks!

    • Marked as answer by Charlie Liao Tuesday, March 31, 2015 8:35 AM
    Tuesday, March 31, 2015 1:11 AM

All replies

  • W/o specifically knowing your exact need, I can tell you that in PowerPivot there is no need to pull in lookups from other tables. Should be just what you need.

    Unrecognized Excel MVP (UEM)

    Monday, March 30, 2015 12:11 AM
  • UEM,

    Well, the lookups (now related) are easy and a fantastic part of PowerPivot. However, the Sumifs aren't looking-up, they are needed to search through 250K plus thousand records on another table by document number, which itself is duplicated usually three times. I do have to monitor each transaction for accuracy across all sytems, so the high level view is nice, but not sufficient. 

    So, in fact, I currently have a Table 1 which contains 136K records (mostly unique document numbers, but not all) which are referenced using a Sumifs to comb through 250K records to find (document number appears at least three times) and return the proper amount (so 136K times 250K calculations...34 billionish just for the Sumifs part if I'm getting that right). This amount is then divided by the count of Document Number in Table 1, to ensure the amount is correct when the Document Number is rolled-up in the pivot table.

    It's a mess, and maybe bad design in the data source, but it's what I have to work with and these are the fastest Excel calculations I know to do the job. If I can duplicate the Sumifs above in PowerPivot language using the Calculate function (and whatever else needs to be involved), then I think I could have something manageable. If I have to go to SQL then maybe I do, but would like to see if PowerPivot can handle it.

    Thanks!

    Monday, March 30, 2015 10:19 PM
  • Update!

    I watched a few videos and figured out a solution (calculated column in Table1):

    =Calculate([Table2 Total Cost],Table1[DocNumber])/[Count of Table1 DocNumber]

    What was 45 minutes took about 5 seconds!! AMAZING!!!

    I am running into a second issue now :( I'm trying to duplicate this EXACT calculated column in Table1 with Table3, but it's kicking circular reference error. I turned on allow circular references, in Excel, but didn't fix the issue. I recreated a calculated measure with a different name (Count of Table1 DocNumber2), but no dice.

    Going to keep trying and reading, but any suggestions are appreciated. Thanks!

    • Marked as answer by Charlie Liao Tuesday, March 31, 2015 8:35 AM
    Tuesday, March 31, 2015 1:11 AM