Portfolio Slicer Refresh Issues

When refreshing Workbooks you might get error message: “A PivotTable Report cannot overlap another PivotTable report”

Pivot overlap error will happen if:

  1. You have more than 16 different “Account” values. To fix either reduce number of the accounts, or review existing reports and add empty rows between pivot table reports.
  2. If you have more than 26 different “Allocation” values. To fix either reduce number of the accounts, or review existing reports and add empty rows between pivot table reports.
  3. (Most common) If existing filters on Pivot Tables were reset for any reason. Most of the reports have the filter on them. For example, in Daily tab the first report is “Daily Portfolio Movements”. Just above that report there is filter “Days-Last 20” with value set to “Yes”. There filters limit how much data is displayed in the report. If for any reason such filter value is set to “All”, then this report will overlap with other reports below. To fix this issue please review EVERY report in ALL worksheets and make
    4. sure that if report has filter, its value is not set to “All”, but to value that would make sense in that context (Most cases value should be set to “Yes”).
  4. Check Minimum Portfolio Slicer workbook date - this date should not be in the future.

Error while refreshing “AttributeID = TransID doesn’t exist”

This error indicates that PowerPivot was not able to load Transactions data into its data model. This usually happens due to following reasons:

  1. You deleted or incorrectly edited first “System” record in the source transactions table (worksheet srcTrans). When PowerPivot loads data into model it uses first x records (could be 8, could be more) to decide what type of data (text, whole number, decimal number) is in each column. Default value is text. So when first x records have no values in the Fee column, then PowerPivot model decides that Fee column will have type of text. But then later in calculations PS will expect decimal number type of that column so you will get error. So first record in Transactions table was created to have values in every single column, so PowerPivot model would know what type of data is in each column. This record must have earliest date out of all of your transactions. It is also recommended that you would order this table by Date column and in some cases you might need to force re-ordering before refreshing data.
  2. If data type in the columns you entered does not match expected data types. For example in Date column you entered string that Excel cannot covert to date in your locale environment. Another common example is when in the column that expects number you entered value that cannot be converted to number in your local settings - for example you entered space (!!!) or used decimal separator that is not recognized by your locale settings (comma instead of dot).

Portfolio Slicer refresh takes long time - how to troubleshoot

We tested Portfolio Slicer with 100 symbols and around 4500 transactions on PC with 12GB of RAM and Intel i7-3770 CPU @ 3.40GHz. Data refresh times were as follows:

  • Excel 2010: Refresh all linked tables in PowerPivot window (starting PowerPivot window can initiate that): 30sec. Refresh external data files: 7 sec. Refresh all reports: 26 seconds. Total time: 63sec.
  • Excel 2013: Refersh all takes 135 sec. Selective refresh for Trans table takes 35 seconds.
  • On Power BI for desktop application all table refresh takes around 10 seconds.
If your data refresh times are significantly longer, you might want to investigate why.

  • If you are having problems with Excel 2013 - please first make sure that your Excel 2013 (that is Office 2013) has latest service pack installed. When Office 2013 was released, it had bug that made Portfolio Slicer run extremely slow. Later Service Pack 1 fixed this issue. You should make sure that your Excel 2013 version is at least 15.0.4569.1506. To see your version start Excel 2013, choose menu “File”, then “Account” and click on “About Excel” button. At the very top you will see Excel version (or you can use this method. Before doing any more tests you must make sure that version of your Excel is 15.0.4569.1506 or higher. If you just recently installed Office 2013, I would encourage you to download and install Office 2013 Service Pack 1 manually and do not rely on Windows Update. So far in majority of cases when users reported extremely slow data refresh performance (20+ minutes), manually installing Office 2013 SP1 fixed issue.
  • On Excel 2013 (or latter version) learn how to do selective refresh and then configure fast refresh.
  • Get your existing performance baseline. On Excel 2013 measure how long it take for you to do selective refresh for Trans table. On Excel 2010 you cannot do selective refresh, so record how long it takes to refresh all reports. After doing any changes below, compare that change impact by measuring how long it takes to do same refresh after change.
  • Go through every report page and review it. If you see reports that generated many data rows, then you need to apply filters to these reports so that they would bring back less data. For example report on page “TransInfo” without any filters will bring back all transactions. So in this case you can click on slicers “Year” and “Month in Year” to bring back transactions just for one month. Less data means less time spend by your computer calculating it.
  • Most expensive calculations are on worksheet “Yearly”. This worksheet has slicer at the top left part of the page “Years-Current”. Make sure value for this slicer is “Yes” - this will calculate just current years data. Any time you want to look at more historical data you simply remove filter from this slicer. Please note that if you computer is fast enough, this slicer will save you just 5-10seconds of time, so it might not be worth having this filter on.
  • Consider option of creating new Account “Empty” and do not assign any transactions to this account. Then for worksheets that you review seldom or that do not apply to you, make slicer selection for this “Empty” account. Reports for these accounts usually will be calculated faster, so these worksheets will add less time to your data refresh. For reference - refreshing just Transactions table on Excel 2013 with all worksheets set to “Empty” account can take 30sec - this is normal and depends on amount of data in your model and your computer.
  • If your computer is running Windows 7 OS or older, then you should be aware that “Control Panel”->“Power Options” can have significant impact on refresh time. Test how much your refresh time is reduced (comparing to baseline test) by changing Power Option to “High Performance”. If you do not see significant impact, restore this setting back.

  • You will have significant performance impact when you configured Portfolio Slicer to track cash and also use “Generated Dividends” feature. This configuration is supported, but report refresh time could be twice as longer (or even more). Try disabling tracking of cash and check how this improve data refresh performance.
  • Download and try to do refresh with Portfolio Slicer for Power BI (PSfPBI). In our tests we saw refresh times on PSfPBI about 5 times faster comparing to Excel. This is because PSfPBI does not refresh all reports at once, but just deals with one page you are currently working with. Also data refresh is more optimized comparing to Excel.