PowerPivot Calculating Measure on semi-aggregated data

[maxxam]

Hi all,

This forum has already helped me so much in the past months but now I have the first question I couldn't find an answer to anywhere else, really hope someone can point me into the right direction.

I have an Excel model where I use a data model and power pivot. Basically user enter the time they worked on a certain project (tblTimes) and there is target number of hours each user should work on a specific projet (tblAllottedTimes).

tblTimes
- Username
- Datestamp
- Project code
- Hours worked

tblAllottedTimes
- Username
- Projec code
- Hours planned

My goal: Create a pivot table with the following structure
Username | Project code | Sum of all hours worked | Number of hours planned | Remaining hours (basically the 4th column - 3rd column)

I succeeded in setting up the relationship between the two tables and create a pivot with the first 4 columns. The 5th however I don't know how to create since I can't create a calculated field when using power pivot.
Also to get the 4th column working I had to use a workaround where I added the Hours planned to the tblTimes via the RELATED function and then in the pivot instead of summing the "Hours planned" I let it show me the MAX value, maybe there is a better way.

So my best guess is that I need to write some kind of DAX function but I didn't find any similar question to orient myself on. Would love if someone could give me some advice.

 

[GraH]

Ideally you have a table with unique project codes. This table is then linked to both fact tables. The same for your usernames.
Then the following measures can be created:

Planned:=SUM(tblAllottedTimes[Hours Planned])
Worked:=SUM(tblTimes[Hours worked])
Remaining:=Planned-Worked

The pivot then takes project codes and usernames from the unique tables.

 

[maxxam]

You are my hero (@GraH) works like a charme . Thank you so much!

 

[GraH]

Thanks for the feedback and mark. Glad I could help.