cancel
Showing results for
Did you mean:

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

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):

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-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!

1 ACCEPTED SOLUTION
Resolver II

Hi Ileana.

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

Please review the attached PBI 🙂

8 REPLIES 8
Resolver II

Hi Ileana.

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

Please review the attached PBI 🙂

Frequent Visitor

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)?

Resolver II

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.

Frequent Visitor

Hi @David-Ganor ,

Frequent Visitor

The relationships are:

-tblProjects-tblAllocation: 1 to many

Super User

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

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Frequent Visitor

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

@ileana2019  how all these tables are connected?

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors