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

PowerBITest.png

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

 

View solution in original post

1 REPLY 1
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])

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors