Sum of Partially Filtered Data (Highly Complex)

[Aylanna]

I am trying to get a measure to calculate properly, and for the most part, it works precisely the way it should.  The measure is:

% Capacity - DIVIDE(SUM(TashData[Hours]),Sum(HCOrgData[Hours/Month])

The Hours from the TashData table is the total hours worked per activity, per month.  The Hours/Month is the target number of hours (based on the number of hours in their standard work week) per month.  E.g. an employee works a total of 280 hours across all activity types in the month of January, and their targeted number of hours was 200 hours based on a 40-hour work week.  The % Capacity calculation should properly show them at 140%.

This measure is used in a table viz where you have:

Employee Name - Total Hours - % Capacity

You can see for each employee what is going on with their work hours and how burdened they are.

In the HCOrgData table, part of the function of the table/data is to get employees in the right org hierarchy, as well as enforce data permissions.  It is time specific, so that there are lines of org information for each month.  If employees don't change organizations, the measure calculates perfectly.  However, if the employee changes organizations and you are looking at aggregate data over multiple months (particularly the full year), that's where there is a problem.  This issue does not occur if you are looking at a single month of data.

If the data is filtered to show a particular part of the organization and that employee has moved at any point, then you get the issue.  The issue is, is that the calculation sums all the employee's available activity for the time period, limits the target hours to those hours under that particular org.  As an example, an employee is in Org A for 4 months, and Org B for 8 months.  If I filter on Org A (or my permissions are only for Org A), then the calculation is 12 months of hours divided by 4 months of target hours.  That shows results of % capacity being at 300% or greater.  For that specific employee, I want it to calculate the total hours of activity for the time period that they were in that organization, even if I'm looking at 12 months of data.

How can I get this to calculate properly?  If it helps, here is the data model and its relationships:

[Anonymous · Accepted solution]

@Aylanna  I'd have to see the data tables to form a stronger opinion on an approach, but within a measure, can you save in a variable the number of months the employee worked in Org A and use that as the number of months of hours divided by the months of target hours?

[Anonymous · Accepted solution]

@Aylanna  I'd have to see the data tables to form a stronger opinion on an approach, but within a measure, can you save in a variable the number of months the employee worked in Org A and use that as the number of months of hours divided by the months of target hours?

[Aylanna]

I could certainly give it a try!  While I can do a decent amount of formula writing, and can program macros, I'm not super familiar with variables and use in DAX.

What would you want to see in the data tables?

[Anonymous]

@Aylanna  All the tables you're using as the source of the calculation, and the viz you're creating with it. Can you redact information and share?

[Aylanna]

Sure!  Is there an email that I should use for the access?  I want to restrict the link.  I redacted out as much as I could and still get you working data.

[Anonymous]

I just sent you a PM with my email address

[Aylanna]

Great!  Thank you.  You should have a link in your email with the PBIX file.

[Aylanna · Accepted solution]

I got it!!!!

Thanks to all your work, plus a little fine tuning from this vid ... DAX and the Start Date End Date Problem aka Events In Progress (youtube.com)

Here is how I have the measure written:

% Capacity =

VAR OrgStartDate = FIRSTDATE(HCOrgData[Month])

VAR OrgEndDate = LASTDATE(HCOrgData[Month])

VAR OrgPeriodHours = CALCULATE(SUM(TASHData[Hours]),TASHData[Timeframe] >= OrgStartDate, TASHData[Timeframe]<=OrgEndDate)

Return DIVIDE(OrgPeriodHours, SUM(HCOrgData[Hours/Month]))