• 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

Creating a dashboard using multiple tables, pivot tables and power queries|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Creating a dashboard using multiple tables, pivot tables and power queries|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 & ChartsCreating a dashboard using multiple…
sp_PrintTopic sp_TopicIcon
Creating a dashboard using multiple tables, pivot tables and power queries
Avatar
Manoj Singh

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 20, 2021
sp_UserOfflineSmall Offline
1
September 20, 2021 - 7:28 pm
sp_Permalink sp_Print sp_EditHistory

I have got two tables capacity and demand

Capacity table looks like this:

| RESOURCE NAME |     SKILL GROUP     |  PROJECT  | START DATE |   END DATE  | COST   PER HOUR | CAPACITY |
|:-------------:|:-------------------:|:---------:|:----------:|:-----------:|:---------------:|:--------:|
|   Resource 1  |  Automation Testing | Project 1 | 1-Oct-2021 | 31-Mar-2022 |       12.0      |   800.0  |
|  Resource   2 |      DB Testing     | Project 1 | 1-Oct-2021 | 31-Mar-2022 |       11.0      |   900.0  |
|   Resource 3  |      DB Testing     | Project 1 | 1-Oct-2021 | 31-Dec-2021 |       12.0      |   800.0  |
|  Resource   4 |    Report Testing   | Project 2 | 1-Oct-2021 | 30-Apr-2022 |       12.0      |   200.0  |
|   Resource 5  |   CICD and Devops   | Project 3 | 1-Oct-2021 | 31-Mar-2022 |       11.0      |   800.0  |
|  Resource   6 | Performance Testing | Project 1 | 1-Oct-2021 | 31-Mar-2022 |       12.0      |   900.0  |
|   Resource 7  |  Automation Testing | Project 2 | 1-Nov-2021 | 31-Mar-2022 |       10.0      |   250.0  |
|  Resource   8 |    Cloud Testing    | Project 3 | 1-Oct-2021 | 31-Mar-2022 |       12.0      |   900.0  |
|   Resource 9  |    Report Testing   | Project 1 | 1-Dec-2021 | 31-Dec-2021 |       11.0      |   800.0  |
| Resource   10 |    Cloud Testing    | Project 1 | 1-Dec-2021 | 31-Dec-2021 |       11.0      |   900.0  |
|  Resource 11  |    Report Testing   | Project 3 | 1-Dec-2021 | 31-Dec-2021 |       12.0      |   800.0  |
| Resource   12 |   Pipeline Testing  | Project 1 | 1-Dec-2021 | 31-Dec-2021 |       11.0      |   900.0  |
|  Resource 13  |    Cloud Testing    | Project 3 | 1-Dec-2021 | 31-Dec-2021 |       12.0      |   800.0  |

 

Demand Table looks like this:

| RESOURCE NAME |     SKILL GROUP     |  PROJECT  | START DATE |   END DATE  | DEMAND |
|:-------------:|:-------------------:|:---------:|:----------:|:-----------:|:------:|
|   Resource 1  |  Automation Testing | Project 2 | 1-Oct-2021 | 25-Oct-2021 |  200.0 |
|  Resource   2 |      DB Testing     | Project 1 | 1-Oct-2021 | 31-Dec-2021 |  300.0 |
|   Resource 3  |      DB Testing     | Project 1 | 1-Oct-2021 | 31-Dec-2021 |  400.0 |
|  Resource   1 |    Report Testing   | Project 1 | 1-Oct-2021 | 31-Dec-2021 |  200.0 |
|   Resource 4  |   CICD and Devops   | Project 3 | 1-Oct-2021 | 31-Mar-2022 |  300.0 |
|  Resource   5 | Performance Testing | Project 2 | 1-Oct-2021 | 25-Oct-2021 |  400.0 |
|   Resource 6  |  Automation Testing | Project 1 | 1-Oct-2021 | 31-Dec-2021 |  200.0 |
|  Resource   2 |    Cloud Testing    | Project 2 | 1-Oct-2021 | 25-Oct-2021 |  300.0 |
|   Resource 7  |    Report Testing   | Project 1 | 1-Oct-2021 | 31-Dec-2021 |  400.0 |
|  Resource   8 |    Cloud Testing    | Project 3 | 1-Oct-2021 | 31-Dec-2021 |  800.0 |
|   Resource 9  |    Report Testing   | Project 2 | 1-Oct-2021 | 31-Dec-2021 |  800.0 |
| Resource   10 |   Pipeline Testing  | Project 1 | 1-Oct-2021 | 31-Dec-2021 |  600.0 |
|  Resource 11  |    Cloud Testing    | Project 3 | 1-Oct-2021 | 31-Dec-2021 |  700.0 |
| Resource   10 | Performance Testing | Project 2 | 1-Oct-2021 | 31-Dec-2021 |  250.0 |
|  Resource 11  |  Automation Testing | Project 1 | 1-Oct-2021 | 31-Dec-2021 |  250.0 |

I merged these two tables using power query on the basis of resource name and trying to generate the below pivot table.

Pivot table screenshot

FhtJl.pngImage Enlarger
My common field in both the table is "Resource Name", what I am trying to build in pivot table which will be further used in my dashboard with slicer. Trying to build dashboard like this. Dashboard screenshot Dashboard ScreenshotImage Enlarger

Challenging points:

  • Not able to capture the Capacity Hours and Total capacity Cost, Cost is getting repeated. Other values from Hours & Cost sections are fine Demand hours & Total Demand as per the pivot table above.
  • Approach to build the project and resource sections, which will show data for all projects & resources and when we select specific "Project" and "Resource" the section should populated data for that selection, also how to build the percentage bar.

Attached the sample data file as well.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 21, 2021 - 11:51 am
sp_Permalink sp_Print

Hi Manoj,

Welcome to our forum. First, the easy question on how to build the percentage bars. Use Conditional Formatting Data Bars.

Regarding your PivotTable, your Demand table has the same resource name duplicated, which makes sense, but when you merge the Demand and Capacity tables you end up duplicating the Capacity amount for each matching resource name in the Demand table. In your example data, resources 1, 2 and 11 are listed multiple times in the Demand table and the total Capacity is listed at the amount at the resource level for each project.

You either need to split the capacity values down to a project level, or only report demand vs capacity at the resource level.

Hope that points you in the right direction.

Mynda

Avatar
Manoj Singh

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 20, 2021
sp_UserOfflineSmall Offline
3
September 21, 2021 - 6:20 pm
sp_Permalink sp_Print

Thanks, Mynda for your prompt reply!

Yes, this is my first post to this forum and I really appreciate that users are getting reply very fast.

Also, I used conditional formatting for displaying the Bars, I did referred to one of your dashboard tutorials. Thanks.

  • Not able to understand the second section where you mentioned to split the capacity values down to a project level. How to achieve this, my capacity is on the basis of resources. and what do you mean by only report demand vs capacity at the resource level, if this the case in my dashboard, i want see the capacity, demand of each resources, projects and skills as well.

Do I need to change my data in my excel the way it is getting captured or I have done something wrong while merging the Capacity and Demand table using power query. I am happy for both the options, my objective is to get the capacity vs demand data for each resources, project and skills. 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 21, 2021 - 6:53 pm
sp_Permalink sp_Print

Hi Manoj,

If you want to see capacity at the project level then you need to change the way your data is being captured so that you're capturing it at the project level, so that it can then be added up to get to the total capacity at the resource level, just like you do for the demand data. 

Mynda

Avatar
Manoj Singh

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 20, 2021
sp_UserOfflineSmall Offline
5
September 21, 2021 - 10:46 pm
sp_Permalink sp_Print

Thanks Mynda!

 

Looks like I am bit confused, could you post sample data and explain bit more to proceed further on this. I got stuck on that part. I don't wish to change the my excel format as this will be the template for all my leads to capture their data.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
September 22, 2021 - 10:28 am
sp_Permalink sp_Print

Hi Manoj,

In the Demand sheet of the attached file I've added a column for Capacity. The values in red are the total capacity split across the two projects for each resource. This is what I mean by you having to capture the capacity at the level you want to report at. i.e. if you want to report at project level, then you need to store your capacity values at project level, which then rolls up to resource level. 

Note: capacity doesn't need to be stored in the same table as demand. I've just put it there to illustrate the point that your capacity values aren't at a low enough level of granularity to support the analysis you want to do.

Mynda

Avatar
Manoj Singh

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
September 20, 2021
sp_UserOfflineSmall Offline
7
September 22, 2021 - 8:32 pm
sp_Permalink sp_Print sp_EditHistory

Hi Mynda,

 

I understand that it would be good to have capacity hours distributed in demand table as per the resources allocated. The challenge is that, I can't put capacity hours in my Demand table, so I would need a way to find how many resources matching in Demand table comparing with Capacity table and then distributing the capacity hours among them to achieve the logic you mentioned it in the attachment above.

I tried this code and was able to achieve above logic

=XLOOKUP([@[RESOURCE NAME]],CapacityTable[RESOURCE NAME],CapacityTable[CAPACITY])/COUNTIF([RESOURCE NAME],[@[RESOURCE NAME]])

Is there better way I can achieve through power query or above code will break in any scenarios.

 

~Manoj

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
September 24, 2021 - 1:05 pm
sp_Permalink sp_Print

Hi Manoj,

You didn't read the 'Note' in my reply where I said you don't need the capacity stored in the demand table. I just put it there to illustrate the point that you need to have capacity at the same level of project detail that you have demand at.

I wouldn't have thought that splitting the capacity by project level is something you can do with a formula because I wouldn't expect capacity to be equally split. However, if you want a crude split like that then you could use Power Query to count the number of projects in each resource and then divide the capacity by the count and apportion accordingly. See example file attached - Final Table query. 

Note: I've used this numbering grouped data in Power Query technique.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: AndyC
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

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