Hi, new user here (of both the forums and Power BI). For some reason my last post was marked as spam which is hilarious seeing the front page/recent posts. Result I want to achieve:I want to show a graph with as columns the resources needed by projects per month (the different roles within the resources are stacked). As a line I want to show the available resources during those months. These are in the tables 'resources_needed' and 'resources_available'. Disclaimer: I slightly changed my column names for clarity. In the examples they are both called 'Uren'. I renamed them for the purpose of this post to Hours_needed and Hours_available, I feel like its still self explanatory in the example beneath. Resources available:This table contains the name, role, date and hours of a resource (e.g. David is a projectmanager, and has 0 hours available in the month of januari 2024). I'm only using the data on monthly/yearly levels (so the day is not important). Person_nameRole_availableDate_resourceHours_availableDavidProMan1-1-20240SandrosBusArc1-1-20240StefanSubMatExp1-1-2024120HermanBusAna1-1-20240David1ProMan1-1-20240Stefan1SubMatExp1-1-202440Hugo2BusAna1-1-20240Linda2InfAna1-1-20240Stefan2SubMatExp1-1-202440JannetjeInfAna1-2-20240PietSubMatExp1-2-202440DavidProMan1-2-20240SandrosBusArc1-2-20240StefanSubMatExp1-2-202440Jannetje1InfAna1-2-202480 Resources needed:This table contains the project name, role needed, date when the resource is needed and hours the resource is needed (e.g. Proj1 needs an information analist (InfAna) for 64 hours in februari of 2024). Again, only using the data on monthly/yearly levels (so the day is not important). Project_nameRole_neededDate_resourceHours_neededProj1InfAna1-2-202464Proj1SubMatExp1-2-202464Proj1BusAna1-2-202432Proj1ProMan1-2-202416Proj1BusArc1-3-202496Proj1InfAna1-3-202464Proj1SubMatExp1-3-202464Proj2BusAna1-3-202432Proj2ProMan1-3-202416Proj2BusArc1-4-202496Proj2InfAna1-4-202464Proj2SubMatExp1-4-202464 My current problem:When I use the line and stacked column chart it doesnt seem to work correctly. The stacked column part works just fine but when I put the line in it shows just a solid straight blue line with 'Sum of Hours' with the total sum of all the hours available as the value. When I create a line diagram of the available resources it works, beneath is a line graph where it shows 'Sum of hours available by month'. When I combine it within the stacked column it stops showing by month and just shows the 'sum of hours available'. As Im new I dont really have a clue as to what is happening. I have formatted my date fields similarly but I still feel like the combination of both date's on the x axis is going wrong somewhere maybe?

The reason you get a flat line is that Power bi has no idea what to do with your line and it just makes it equal to everything (so if that value = 15, you'd see 15 for every role/date).I'm thinking we can solve your issue by adjusting your tables and creating some relationships! We need some dimension tables. If you're unfamiliar, they're just tables that store attributes that define our big set of data.Starting with the data you posted here we can make a role dimension table: Right click on your resources_needed table and duplicate it. Rename 'Role_needed' to 'Role_available' Select Append Queries and append the resources avaible table Since the column names match, all of your roles will now be in one column. Select that column > right click the column header > remove other columns Right click again, and remove duplicates. Rename your column to Role and your table to something like Role_dimension Then to create a date dimension table... (I like using this script since it gives you a number of useful columns https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query) New Source > Blank Query > Open advanced editor and copy/paste the script >rename to date or whatever floats your boat. Change the ToYear (near the top) to a date in the future You should have 4 tables! Close & Apply to exit power query. For relationships: Open the Model view Drag and drop from your dimension tables into your "Fact" tables (where your actual data is located) For role, you only have one so relate it to each of the roles in each table Do the same for date, just use the 'Date' at the top of the table Model should look something like this Use your dimension tables in your visuals Let me know how it goes!

Pascal_15

Regular Visitor

2 years ago

Solved

Line and stacked column chart - line not working with data

Hi,

new user here (of both the forums and Power BI). For some reason my last post was marked as spam which is hilarious seeing the front page/recent posts.

Result I want to achieve:

I want to show a graph with as columns the resources needed by projects per month (the different roles within the resources are stacked). As a line I want to show the available resources during those months. These are in the tables 'resources_needed' and 'resources_available'.

Disclaimer: I slightly changed my column names for clarity. In the examples they are both called 'Uren'. I renamed them for the purpose of this post to Hours_needed and Hours_available, I feel like its still self explanatory in the example beneath.

Resources available:

This table contains the name, role, date and hours of a resource (e.g. David is a projectmanager, and has 0 hours available in the month of januari 2024). I'm only using the data on monthly/yearly levels (so the day is not important).

Person_name	Role_available	Date_resource	Hours_available
David	ProMan	1-1-2024	0
Sandros	BusArc	1-1-2024	0
Stefan	SubMatExp	1-1-2024	120
Herman	BusAna	1-1-2024	0
David1	ProMan	1-1-2024	0
Stefan1	SubMatExp	1-1-2024	40
Hugo2	BusAna	1-1-2024	0
Linda2	InfAna	1-1-2024	0
Stefan2	SubMatExp	1-1-2024	40
Jannetje	InfAna	1-2-2024	0
Piet	SubMatExp	1-2-2024	40
David	ProMan	1-2-2024	0
Sandros	BusArc	1-2-2024	0
Stefan	SubMatExp	1-2-2024	40
Jannetje1	InfAna	1-2-2024	80

Resources needed:

This table contains the project name, role needed, date when the resource is needed and hours the resource is needed (e.g. Proj1 needs an information analist (InfAna) for 64 hours in februari of 2024). Again, only using the data on monthly/yearly levels (so the day is not important).

Project_name	Role_needed	Date_resource	Hours_needed
Proj1	InfAna	1-2-2024	64
Proj1	SubMatExp	1-2-2024	64
Proj1	BusAna	1-2-2024	32
Proj1	ProMan	1-2-2024	16
Proj1	BusArc	1-3-2024	96
Proj1	InfAna	1-3-2024	64
Proj1	SubMatExp	1-3-2024	64
Proj2	BusAna	1-3-2024	32
Proj2	ProMan	1-3-2024	16
Proj2	BusArc	1-4-2024	96
Proj2	InfAna	1-4-2024	64
Proj2	SubMatExp	1-4-2024	64

My current problem:

When I use the line and stacked column chart it doesnt seem to work correctly. The stacked column part works just fine but when I put the line in it shows just a solid straight blue line with 'Sum of Hours' with the total sum of all the hours available as the value.

When I create a line diagram of the available resources it works, beneath is a line graph where it shows 'Sum of hours available by month'. When I combine it within the stacked column it stops showing by month and just shows the 'sum of hours available'.

As Im new I dont really have a clue as to what is happening. I have formatted my date fields similarly but I still feel like the combination of both date's on the x axis is going wrong somewhere maybe?

Need Help

Syk
2 years ago
The reason you get a flat line is that Power bi has no idea what to do with your line and it just makes it equal to everything (so if that value = 15, you'd see 15 for every role/date).

I'm thinking we can solve your issue by adjusting your tables and creating some relationships! We need some dimension tables. If you're unfamiliar, they're just tables that store attributes that define our big set of data.
Starting with the data you posted here we can make a role dimension table:

Right click on your resources_needed table and duplicate it.

Rename 'Role_needed' to 'Role_available'

Select Append Queries and append the resources avaible table

Since the column names match, all of your roles will now be in one column.

Select that column > right click the column header > remove other columns

Right click again, and remove duplicates.

Rename your column to Role and your table to something like Role_dimension

Then to create a date dimension table... (I like using this script since it gives you a number of useful columns https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query)

New Source > Blank Query > Open advanced editor and copy/paste the script >rename to date or whatever floats your boat.

Change the ToYear (near the top) to a date in the future

You should have 4 tables! Close & Apply to exit power query.

For relationships:

Open the Model view

Drag and drop from your dimension tables into your "Fact" tables (where your actual data is located)

For role, you only have one so relate it to each of the roles in each table

Do the same for date, just use the 'Date' at the top of the table

Model should look something like this

Use your dimension tables in your visuals

Let me know how it goes!

4 Replies

Syk
Resident Rockstar
2 years ago
Are these using 2 different tables?
I'm suspecting you are using these from the first table:

Uren: for column values

Role available: for legend

Date resource: for x-axis

Then you're using Uren from the second table as your line? Do you have any relationships set up as well?
- Pascal_15
  Regular Visitor
  2 years ago
  Yeah these are two different tables. There is no relation set between them as I didn't know what kind of relation would apply (no unique values are available in either table). I wanted to sort of indicate that both role and date were the same kind of variable but didn't know how to and whether that was necessary.
  
  The data comes from the two different tables, reaources_needed and resources_available.
  
  Im using this variables from the first table:
  Hours_available (uren): y line
  
  These from the second table:
  hours_needed (uren): as column value
  Role_needed: legend
  Date_resource: x axis
  - Syk
    Resident Rockstar
    2 years ago
    The reason you get a flat line is that Power bi has no idea what to do with your line and it just makes it equal to everything (so if that value = 15, you'd see 15 for every role/date).
    
    I'm thinking we can solve your issue by adjusting your tables and creating some relationships! We need some dimension tables. If you're unfamiliar, they're just tables that store attributes that define our big set of data.
    Starting with the data you posted here we can make a role dimension table:
    
    Right click on your resources_needed table and duplicate it.
    
    Rename 'Role_needed' to 'Role_available'
    
    Select Append Queries and append the resources avaible table
    
    Since the column names match, all of your roles will now be in one column.
    
    Select that column > right click the column header > remove other columns
    
    Right click again, and remove duplicates.
    
    Rename your column to Role and your table to something like Role_dimension
    
    Then to create a date dimension table... (I like using this script since it gives you a number of useful columns https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query)
    
    New Source > Blank Query > Open advanced editor and copy/paste the script >rename to date or whatever floats your boat.
    
    Change the ToYear (near the top) to a date in the future
    
    You should have 4 tables! Close & Apply to exit power query.
    
    For relationships:
    
    Open the Model view
    
    Drag and drop from your dimension tables into your "Fact" tables (where your actual data is located)
    
    For role, you only have one so relate it to each of the roles in each table
    
    Do the same for date, just use the 'Date' at the top of the table
    
    Model should look something like this
    
    Use your dimension tables in your visuals
    
    Let me know how it goes!

Forum Discussion

Line and stacked column chart - line not working with data

4 Replies

Related content

Stacking Data Columns

Stacked Column Chart Data Labels

Need a stacked clustered column chart

Stacked Column Chart Data Limit Issue

Stacked column