Line and stacked column chart - line not working with data

[Pascal_15]

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?

[Syk]

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]

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 · Accepted solution]

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]

Crazy stuff, thanks for your thorough reply, it really helped me get a better grasp of the workings of power bi and the relational model. Thanks a lot, much appreciated!