cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gmeronek Frequent Visitor
Frequent Visitor

contribution to whole filtering problem

I have a puzzle that I'm struggling to categorize properly in data modeling terms, as I'm hoping with the right terms, I'll find the right solution.

 

In the simplified diagram below, my tabular model Fact WIP has one row per time entry for each employee that captures the work they do for a client. It's easy to show measures like total Billable Hours per client, or total billable hours per employee, or even a breakdown like :

 

Fiscal Period 2020-01

AdventureWorks Inc: 23 hrs

  • Lana: 12 hrs
  • Jamil: 5 hrs
  • Maura: 6 hrs

However, I need to create a way that Maura's manager can show a report of all the clients that she worked on in a given period PLUS the way her contribution compares to the whole, ideally broken out by the skill levels involved (think senior, junior, intern, etc.). Maura's manager is going to want a simple way to filter to Maura and get totals for her, but also totals for work done by others for the same client & fiscal period.

 

This doesn't seem like a "bridge table" problem, as the grain of the transactions allows me to represent 1 record per employee already. However, filtering Maura's clients while still getting the contributions of other employees for those clients is giving me pause. Do I need a separate factless fact establishing the list of all Maura's clients for a period that is separate from the total hours of work done for all clients?

Thanks in advance for your help!

 

Generic Overview.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
gmeronek Frequent Visitor
Frequent Visitor

Re: contribution to whole filtering problem

Thanks! The solution ended up being a variation on this, though I will also take a look at the suggested solution re: average across all to see if that could help as well. https://www.youtube.com/watch?v=IfC7gBfod50&feature=youtu.be

View solution in original post

2 REPLIES 2
Super User II
Super User II

Re: contribution to whole filtering problem

@gmeronek  - You can find the average hours for all of the employees for whatever filters are in context (and specify skill level based on selected employee's skill level) with the following Measure:

Billable Hours (Average) = 
var _skill = MAX(Employee[Skill Level])
return CALCULATE(
    AVERAGE(WIP[Billable Hours]),
    ALL(Employees[Employee Name]),
    Employee[Skill Level] = _skill
)
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson
gmeronek Frequent Visitor
Frequent Visitor

Re: contribution to whole filtering problem

Thanks! The solution ended up being a variation on this, though I will also take a look at the suggested solution re: average across all to see if that could help as well. https://www.youtube.com/watch?v=IfC7gBfod50&feature=youtu.be

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors