Bad performance updating Pivottables

natasja

New Member
Joined
Feb 26, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have created below model. Sales-data containes 600K rows, Dcalender 750 rows, Item info 1700 rows, Customer info 2000 rows, and remaining dimension tables app 25 rows.
2 calculated columns in DCalender, all other calculations have been created with DAX measures or M language custom columns.
I tested refresh All data both in Excel 32 and 64 bit, but no difference in performance is noted.
Loading the data only takes 60 seconds, but it takes app 12 minutes to refresh the pivot tables.
All queries are waiting for other data model queries.

Application Excel 365
There are 7 sheets, in total 20 Pivot tables, no slicers are applied. When I applied a slicer it slowed down the overall performance.
Privacy settings are disables, fast load enabled, background data disabled.

Can anyone please advise what am I doing wrong ?

Many thanks in advance for your support.
KR - natasja


Model.png
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,260
Pivot tables take time to refresh. Slicers will definitely make it worse, but if you turn of visual indication of values, they won’t take any more time. what takes a lot of the time is rendering the tables. How many data points do you have in total (tables x columns x rows). The bigger this number, the slower it is going to be. It’s easy to test. Take a copy of the workbook, delete all but 1 pivot and see how long it takes.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,658
Messages
5,637,607
Members
416,976
Latest member
LL1300

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top