cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculate two fields based on a filter from the dashboard

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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])```

Anonymous
Not applicable

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])```

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors