Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ileana2019
Frequent Visitor

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

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_DateEnd_DateYear_AAllocation_in_DaysEmployeeProject
01-01-2131-12-21202140AnnaProject 1
01-01-2131-12-212021135LindaProject 1
01-01-2231-12-22202260AnnaProject 1
01-01-2231-12-22202295LindaProject 1
01-01-2231-12-22202215HansProject 1
      
tblProjects     
TitleStart DateEnd Date   
Project 119-10-2030-12-22   
      
tblTasks     
TitleProjectStart DateDue DateCost In Days 
Task 1Project 110-05-2106-08-2195 
Task 2Project 110-01-2225-02-2230 
Task 3Project 116-05-2210-06-2215 
Task 4Project 106-06-2217-06-220 
Task 5Project 130-05-2203-06-220 
Task 6Project 130-08-2110-12-2135 
Task 7Project 125-04-2206-05-2214 
Task 8Project 109-05-2213-05-225 
Task 9Project 120-06-2224-06-225 
Task 10Project 128-02-2218-04-2231 

 

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 119-10-2030-12-22230345

 

The relationships are:

-tblProjects-tblTasks: 1 to many

-tblProjects-tblAllocation: 1 to many

 

And have a filter for the tblTasks Start Date year:

ileana2019_0-1638980553224.png

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!

1 ACCEPTED SOLUTION
David-Ganor
Resolver II
Resolver II

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)

 

DavidGanor_1-1638991590278.png

 

 

DavidGanor_0-1638991469631.png

 

View solution in original post

8 REPLIES 8
David-Ganor
Resolver II
Resolver II

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)

 

DavidGanor_1-1638991590278.png

 

 

DavidGanor_0-1638991469631.png

 

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! 

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.

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
Frequent Visitor

The relationships are:

-tblProjects-tblTasks: 1 to many

-tblProjects-tblAllocation: 1 to many

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Super User
Super User

@ileana2019  how all these tables are connected?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.