Calculate two fields based on a filter from the dashboard

[Anonymous]

Trying to wrap my head around this one... it seems so easy when you look at the tables, but trying to get it to work in PowerBI is driving me crazy.  Hopefully, someone can help me out, 🙂

I have three tables:

- Work Table (Basically each row has a time entry, Date, Team, Time Logged, Start of Week)

- Team Table (Team colour, How many people in the team)

- Start of Week Table (Start of week, How many "work" days in the week, ExpectedWorkHours)

There are relationships between the tables:  Work Table as a link for Start of the Week and Team

Ok so here is my problem:

Example tables:

I need to calculate based on the number of teams members how much expected time should be logged for the week.

For example, if there are 3 team members (Black) and 5 Work Days, with an ExpectedWorkHrs of 6 then my expected work hours for Team Black for the 31October week would be 90 hours a week.

I need this value to be dynamically driven based off of my filter on the dashboard.  So if I select the team Black and Yellow and date range then the expectedworkhrs needs to dynamically change based off of those filters.

When I tried to use calculate to make it work, it kept summing up the entire members table and multiplying that by the expectedworkhrs and ignored the filters on the dashboard.

This seems so simple in my head to make work, but I just cant figure it out, 😞  Any help and assistance would be greatly appreciated and hopefully I have explained my "issue" well enough.

[Anonymous · Accepted solution]

Hi,

If the "work table" is your master one, I think you can create a column using lookup to get the values from other tables, and then a simple sum of this column can work with any filter (with relashionship)

Expected Hours = LOOKUPVALUE(TeamTable[TtlMembers];'Team Table'[Team];[Team]) 
		* LOOKUPVALUE(StartWeek[Workdays];StartWeek[Startofweek];[Startofweek])
		* LOOKUPVALUE(StartWeek[ExpectedWorkHrs];StartWeek[Startofweek];[Startofweek])