Calculate SUM of allocation over a related table with dynamic year filter

[ileana2019]

Hello,

I am having troubles calculating the sum of Allocation from a related table, based on the year filter selection.

I have the following SharePoint lists (I used Excel to simplify):

tblAllocation
Start_Date	End_Date	Year_A	Allocation_in_Days	Employee	Project
01-01-21	31-12-21	2021	40	Anna	Project 1
01-01-21	31-12-21	2021	135	Linda	Project 1
01-01-22	31-12-22	2022	60	Anna	Project 1
01-01-22	31-12-22	2022	95	Linda	Project 1
01-01-22	31-12-22	2022	15	Hans	Project 1
tblProjects
Title	Start Date	End Date
Project 1	19-10-20	30-12-22
tblTasks
Title	Project	Start Date	Due Date	Cost In Days
Task 1	Project 1	10-05-21	06-08-21	95
Task 2	Project 1	10-01-22	25-02-22	30
Task 3	Project 1	16-05-22	10-06-22	15
Task 4	Project 1	06-06-22	17-06-22	0
Task 5	Project 1	30-05-22	03-06-22	0
Task 6	Project 1	30-08-21	10-12-21	35
Task 7	Project 1	25-04-22	06-05-22	14
Task 8	Project 1	09-05-22	13-05-22	5
Task 9	Project 1	20-06-22	24-06-22	5
Task 10	Project 1	28-02-22	18-04-22	31

What I would like is to display in a Power BI table the following:

Title (tblProjects)	Start Date (tblProject)	End Date (tblProjects)	Cost In Days (tblTasks)	Allocation_in_Days (tblAllocation)
Project 1	19-10-20	30-12-22	230	345

The relationships are:

-tblProjects-tblTasks: 1 to many

-tblProjects-tblAllocation: 1 to many

And have a filter for the tblTasks Start Date year:

Whenever I select a year, the Allocation_in_Days should be calculated accordingly (example: if I select year 2021, the Allocation_in_days should be 175). That is now not the case; How can I do that?

Thank you!

[David-Ganor · Accepted solution]

Hi Ileana.

A dimension should be created for project and year (concatenated).

Please review the attached PBI 🙂

https://drive.google.com/file/d/1nQA-eDHIet1AbcIJW8IlxiZ3lOorSB4A/view?usp=sharing

(download)

[ileana2019]

Hi @David-Ganor ,

Thank you for the file! How do I create the Dim Project_Year table? Is that a left outer join between tblAllocation and tblProjects? From where is the Year column inside table Dim Project_Year coming from? From tblAllocation or tblTasks? It should come from tblTasks (since the filter needs to filter for the tasks that start in a certain year). Is there another way than creating the intermediate table (like with using CALCULATE, FILTER, RELATEDTABLE etc)?

Thank you for your help! 

[David-Ganor]

Hi @ileana2019 

The logical reason for creating it as a Dimension - is somewhat a "business" logic.

Every project has tasks and employees that can contribute to this project

Every task (within project)- should be done in a certain year - and has its cost

Every employee can contribute a certain number of days for a certain project in a certain year.

Project --> Task --> Year & Cost

Project -->Employee --> Year & Contribution.

Therefore the Project_Year_Key is a Shared dimension for the "Cost" (tblTasks) and the "Contribution" (tblAllocation).

It should filter both. And performance wise - it should be more efficient than trying to "manipulate" it inside a measure.

Regarding the creation of this dimension, I've got a few options/suggestions:

1.  creating "Year" table (kind of dimDate table) and Cartesian join it with tblProject - this way you generate all possible options
2. Distinct - of Year & Project from tblTask (can be done as a calculated table OR in the Power Query)
3. Use option no. 2 BUT Union with Year & Project from the tblAllocation (remove duplicates after Union) -- for a case where there can be an employee which has the capability to contribute but there are no relevant tasks for him to participate in.

Hope it helps.

If you need further assistance with creating that Dimension - please reply.

[ileana2019]

Hi @David-Ganor ,

After your first reply, I spent some time today to read more about the star schema modeling, fact tables and dimensions tables. I also created the dimension table using a query: selectcolumns where I selected the year and project from tblAllocation (but now that I read your post, it should have been from tblTasks), then I added the key projectyear(by concatenating the 2 columns), then i removed the duplicates. Anyhow, your solution totally worked! Thank you so much! It made me also rethink my entire data modeling, where I was using a lot of different relationships between the tables and a lot of calculations (like lookups, related etc.). I will now try to implement the star schema, I guess a new question post will follow soon. I ll mark your response as solution. Thank you again for taking the time to help with this!

[ileana2019]

The relationships are:

-tblProjects-tblTasks: 1 to many

-tblProjects-tblAllocation: 1 to many

[smpa01]

@ileana2019  a measure with SUM should give you what you need

[ileana2019]

Hi @smpa01 

Indeed the sum works to have the total for all years (inside tblAllocation); I also created a measure: 

CALCULATE(SUMX(RELATEDTABLE(tblAllocation), tblAllocation[Allocation_in_Days])) that gives the same result; However, when I filter(=slicer) on the Year (of the tbTasks StartDate) then the sum is still the total sum. It should be the sum only for that specific year (inside tblAllocation).

[smpa01]

@ileana2019  how all these tables are connected?