Row totals not showing in graphs

[JEM]

Hi, I have searched and read so many articles from this forum and others, but cannot get this to work and I've tried several different solutions to similar problems to no avail. I appreciate any help.

Problem: I need to show accrued hours against estimated hours for each department and also a rollup of the total estimated vs. total accrued. My thought was to show it as a bar/line combo, but it's always show the sum of the column not filtered by the project number. 

Data:

Estimated Hours
Project ID	Dept 1 Hours	Dept 2 Hours	Dept 3 Hours
A123	155	137	121
A124	384	176	160
A125	110	112	80
A126	155	137	121

Accured Hours
Project ID	Dept 1 Hours	Dept 2 Hours	Dept 3 Hours
A123	60	55	125
A124	250	25	150
A125	90	95	50
A126	100	130	100

Things I've tried:

- In the query editor, added a column to sum the row, which shows the right total but the graphs still show the column total.

- Tried unpivoting the hours, which made 3 columns: Project, Department, Hours - same results

- Found several solutions that involved a custom measure that is based on HASONEFILTER, but I could not get this to work. All the examples included a date filter and I do not have a date that is needed in this scenario. Do I need a date?

Thank you!

[JEM]

I had to change the relationship and then unpivot (gave me an error), but it worked after that. I changed the relationship type and now it seems to be pulling everything correctly. Thank you!

[amitchandak · Accepted solution]

@JEM , unpivot the table and name hours column differently and you can merge

or analyze them with the common product and department dimensions