• 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

Gantt Chart Issue|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Gantt Chart Issue|Power Pivot|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 ForumPower PivotGantt Chart Issue
sp_PrintTopic sp_TopicIcon
Gantt Chart Issue
Avatar
David Ford
Timișoara, Romania
Member
Members
Level 0
Forum Posts: 12
Member Since:
November 16, 2018
sp_UserOfflineSmall Offline
1
December 9, 2018 - 7:30 pm
sp_Permalink sp_Print

Hello All

I'm trying to develop a PT to display a Gantt chart.  I found this article on powerpivotpro which features this Gantt:

Gantt1a.jpgImage Enlarger

So I downloaded the workbook and figured I could use this as a basis for my chart.  Now, I'm the first to admit that my understanding of pivot tables is basic, though it's in different league compared with my understanding of DAX.  Nevertheless, where there's a will there's a way (or so I naively thought).  So I set about trying to adapt this measure formula:

Gantt1.jpgImage Enlarger

Note the comment below the code! (but this isn't a question about DAX - please read on.)

I've no need for the "multiplier" or the "Include" - I'll be happy if the measure simply produces a series of "1"s to which I can then apply conditional formatting.  Anyway, after much trial and many errors, I managed to produce this

Gantt2.jpgImage Enlarger

When I dropped the Gantt measure on to my simple pivot table:

Gantt2a-1.jpgImage Enlarger

Instead of displaying "1" under each day for each activity, it displays the total number of days between PlanStart and PlanFinish.  Note that the under COLUMNS I only have ProjectDate, which comes from my Dates table.

After spending a whole day changing the Gantt measure formula, in an attempt to make it display "1"s, I gave up and took another look at the original Gantt pivot table.  I removed the Week_Ending, Day_Of_Week and Day2 from COLUMNS and replaced it with Date:

Gantt3.jpgImage Enlarger

This now displays the number of elapsed days between Start and Finish (though the number is modified by the Multiplier so that, for example, the 8 days between 24/07/2012 and 31/07/2012 becomes 16.

So my "error" was nothing to do with the Gantt measure, it's something to do with the columns in the PT.

I just can't get my head around this - can anyone help me out in understanding what's happening here, before I lose the little hair that i have left?

In desperation,

David

PS. None of the questions in the last few years to the original post on powerpivotpro have been answered; that's why I'm not asking on there.

sp_PlupAttachments Attachments
  • sp_PlupImage Gantt1.jpg (49 KB)
  • sp_PlupImage Gantt2.jpg (40 KB)
  • sp_PlupImage Gantt1a.jpg (61 KB)
  • sp_PlupImage Gantt2a-1.jpg (98 KB)
  • sp_PlupImage Gantt3.jpg (88 KB)
sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
December 10, 2018 - 7:32 pm
sp_Permalink sp_Print

Hi David,

Are you able to share your Excel file? You can anonymize the data if it's sensitive.

Mynda

Avatar
David Ford
Timișoara, Romania
Member
Members
Level 0
Forum Posts: 12
Member Since:
November 16, 2018
sp_UserOfflineSmall Offline
3
December 11, 2018 - 9:36 pm
sp_Permalink sp_Print

Hi Mynda

Thanks for the response.

Workbook is too large to attach; you should be able to access it using this link.  The sheet with the Gantt chart is, imaginatively, named Gantt and the measure is in table tblsubactivities in the PP model.

This workbook contains the model as it was when I wrote the original post.  Since then I've changed the structure, though the problem I described is still there.

Regards

Looking forward to your words of wisdom!

David

 

David

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
December 11, 2018 - 10:12 pm
sp_Permalink sp_Print

Hi David,

I'm unable to open the file from that link. It tries to open in Excel Online, then says the file is too big and offers me the option to open in Excel on the Desktop, but it doesn't work!

Are you able to give me a download link either from OneDrive or DropBox or WeTransfer or similar?

Mynda

Avatar
David Ford
Timișoara, Romania
Member
Members
Level 0
Forum Posts: 12
Member Since:
November 16, 2018
sp_UserOfflineSmall Offline
5
December 13, 2018 - 8:46 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mynda

Sorry for the problems with the OneDrive file.

I've sent a WeTransfer message to website at myonlinetraininghub.com

Thanks again, and sorry for wasting your time.

David

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
December 13, 2018 - 10:46 pm
sp_Permalink sp_Print

Hi David,

You can change your formula to the one below if you just want a 1 to show up where there is activity on a particular date:

=IF(
    COUNTROWS(VALUES(tblSubActivities[SubActCodeDesc]))=1,
          IF(LASTDATE(VALUES(tblDates[ProjectDate]))>=LASTDATE(VALUES(tblSubActivities[PlanStart]))
          &&LASTDATE(VALUES(tblDates[ProjectDate]))<=LASTDATE(VALUES(tblSubActivities[PlanFinish])),
   CALCULATE(1,
          DATESBETWEEN(tblDates[ProjectDate],MAX(tblSubActivities[PlanStart]),MAX(tblSubActivities[PlanFinish])))))

 

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
David Ford
Timișoara, Romania
Member
Members
Level 0
Forum Posts: 12
Member Since:
November 16, 2018
sp_UserOfflineSmall Offline
7
December 14, 2018 - 6:00 pm
sp_Permalink sp_Print

Hi Mynda

Thanks - works a treat!

So, was the original method over-complicating things unnecessarily?

And, if I may, one last question.  My workbook contains a fair number of sheets but, in the scheme of things, not that much data.  Is there something "wrong" with my model, or should I expect such an amount of data to lead to a 6 MB file?

Thanks again!

David

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
December 14, 2018 - 9:37 pm
sp_Permalink sp_Print

The original method allowed for different markers for overdue, complete etc. which you didn't need.

Things that can make a workbook large:

- Formulas in tables. Where you don't need to maintain the formulas, copy and paste them as values.

- I notice you have a Dates table calculated in Excel. It is more efficient to do this in Power Query and then load straight to Power Pivot, thus removing the formulas and the duplicated data in the worksheet.

- Power Pivot is good at compressing columns of data where there are many of the same values, but where there are a lot of unique values then compression is limited. e.g. it will be good at compressing the tblDates because only the first column contains unique values, but the CostItems table won't compress as well because there will be few columns with a lot of duplicates.

- Keep in mind that your data is in the file twice, once in the Tables in the worksheets and again in the Power Pivot model. If your data comes from another system then consider using Power Query to get it and put it in Power Pivot. You could even leave the data in one Excel file and connect to it from another file that only contains your data model and analysis.

I hope that gives you some ideas.

Mynda

Avatar
David Ford
Timișoara, Romania
Member
Members
Level 0
Forum Posts: 12
Member Since:
November 16, 2018
sp_UserOfflineSmall Offline
9
December 15, 2018 - 4:50 pm
sp_Permalink sp_Print

Thanks Mynda - much appreciated!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria
Guest(s) 12
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:
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27238

 

Member Stats:
Guest Posters: 49
Members: 31891
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.