• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to consolidate Dashboard raw data|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / How to consolidate Dashboard raw data|Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsHow to consolidate Dashboard raw da…
sp_PrintTopic sp_TopicIcon
How to consolidate Dashboard raw data
Avatar
James Niven
Member
Members
Level 0
Forum Posts: 16
Member Since:
October 25, 2013
sp_UserOfflineSmall Offline
1
July 18, 2018 - 9:23 pm
sp_Permalink sp_Print

Hi All,

I have a number of dashboards that I have created after going through Mynda's dashboard course.

My raw data I add to the dashboard daily is now getting to over 38,000 rows each on 2 tabs and is making the excel spreadsheet size to 15meg. The data ranges from 2016 through to present date.

I want to keep the year over year comparison, but is there are way to somehow consolidate each year 2016 and 2017 to a small number of rows and then delete these rows after consolidation but still show year over year comparison via the pivot tables and charts?

Thanks

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
July 19, 2018 - 9:45 am
sp_Permalink sp_Print

Hi James,

You have a few options.

1. Use Power Pivot to store the data. In fact, put the source data in a separate file and get it with Power Pivot, that way it's only in the file once, i.e. in the Power Pivot model. Plus Power Pivot is more efficient at compressing data than Excel.

2. If your historical data is in its own tab i.e. it's in one of the 2 tabs you mention, then you could use a PivotTable to summarise the data and then delete the sheet containing the source data as the data will now be stored in the Pivot Cache, so there's no need to also have it in the worksheet.

3. Similar to option 2; move all of your source data to an external file and use PivotTables to analyse it. That way the data is only stored in the file once i.e. the Pivot Cache.

4. Use Power Query to group the historical data into less rows. Depending on the level of detail, this may not result in much compression.

I hope that gives you some ideas.

Mynda

Avatar
James Niven
Member
Members
Level 0
Forum Posts: 16
Member Since:
October 25, 2013
sp_UserOfflineSmall Offline
3
July 19, 2018 - 11:17 am
sp_Permalink sp_Print

Hi Mynda,

Thanks so much for the reply, these are very interesting options.

So, the options you have mentioned above, will there be any issue with other users using the dashboard, because this is shared with other Managers in the building.

Thanks again, I will review when I return to work tomorrow morning...

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
July 19, 2018 - 9:17 pm
sp_Permalink sp_Print

Hi James,

If you use the Power Pivot option then the other users also need a version of Excel that supports Power Pivot, and preferably the same version as yours because it is not backward compatible. i.e. Power Pivot models built in Excel 2013 or later will not work in Excel 2010.

The other options will be compatible with other users.

Mynda

Avatar
James Niven
Member
Members
Level 0
Forum Posts: 16
Member Since:
October 25, 2013
sp_UserOfflineSmall Offline
5
July 19, 2018 - 11:42 pm
sp_Permalink sp_Print

Thank Mynda!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Chandler Davis, Atos Franzon
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.