Need DAX Help - Percentage calculation - Row by Row - Using multiple sources

[mtassone]

Can someone help me write a dax formula that will allow me to take the field "Billable Hrs" in one source and divide it by another field "Possible Hrs" from another source.

I want the formula to work on a row by row basis and then be allowed to sum by certain dates (Days, Weeks, Months).

The current measure I am using = SUM('Performance to Plan - 2017'[Billable Hrs])/SUM('Day'[Hrs Possible]) does not allow me this functionality. It currently on sums for all billable hrs by just 8 hours a day for the time period I select. But if I have 10 users billing 8 hrs a day for 1 day it should be 80 billable hrs / 80 hrs possible.

So = (Number of users * Billable Hrs * Number of days selected) / (Number of users * 8 Hrs Possible * Number of days selected)

This formula does not sum by a row to row basis and ends giving me way too large of a percentage for what I am looking for and for each date range that I select or slice for.

The screenshots below depict the fields from different data sources - "Billable Hrs" from Performance to Plan and "Hours Possible" from Date. Please let me know if you have any ideas as to how I can figure this solution out. I am also filtering by Department Name, User Name, and Date and would like to see what Each of these filters are for Billable Hrs / Possible Hrs.

[spuder]

@mtassone

unfortunately i don't have your whole data so I'm not sure if it works.

Try the following code for the HRS Possible

=SUMX('Day';SUMX(FILTER(Performance_to_Plan;[Entry Date]<=[Date]);'Day'[Hrs Possible]))

As I said. This should give you the total hours possible. And with them you should be able to calculate the percentage value in a separate measure.

[mtassone]

Hey @spuder I really appreciate the help  and the formual seems to be close to what I am looking for (: . The issue is that if you look in the below screenshot and tables;

1) "Hrs possible" should be correct when only one user is selected. Currently it is correct.

2) "Spuder" is the measure you provided me. Spuder = SUMX('Day',SUMX(FILTER('Performance to Plan - 2017',[Entry Date]<=[Date]),'Day'[Hrs Possible])). I need this formual to allow me to multiple select users or select by department name to then say between the date range of (1/9/17) to (1/13/17) which is a 40 hour work week for one person, but a 240 hour work week for 6 people in managed services then what is their combined billable hrs over the 240 hr work week.

The 'Day' data set consists of a date column - (1/1/17) through (12/31/17) and Hrs Possible column for which I simply put 8 hrs in for every work day - Excluding weekends and holidays. This was done manually in excel.

The performance to Plan data set has a 'user name' column, 'Department Name' column, 'Entry Date' column, and "Billable Hrs"

column for the corresponding entry date. Also user names can have multiple records for the same date they can bill some hours on one task and some hours on another task. Not sure if this matters or not.

@spuder let  me know if I can give you aditional information. I really want to be able to figure this out and appreciate the help.

[v-ljerr-msft]

Hi @mtassone,

Could you try the formula below to see if it works in your scenario? 

measure1 =
DIVIDE (
    SUM ( 'Performance to Plan - 2017'[Billable Hrs] ),
    SUMX ( 'Performance to Plan - 2017', RELATED ( 'Day'[Hrs Possible] ) )
)

Regards

[mtassone]

Hey @v-ljerr-msft thank you for your help, I rellay appreciate it. The calculation still seems to be off when it comes to "Hrs Possible"

The Sum of billable Hrs calculation works just fine - 

Sum of Billable Hrs = SUM('KPI - Performance To Plan'[Billable Hrs])

I did manage to write a simple formula for Total Hrs Possible and it is correct for everyone, but only within certain time frames. For instance as long as the usernames have had records starting at the begining of the model (4/1/2016) the total hrs are correct. But if the user name starts at a later date the formula gives them the same amount of Total Hrs Possibel as everyone else and will bring their utilization down, thus bringing down the department utilization.

My total Hrs Possible calculation is below - 

Total Hrs Possible = SUM('Day'[Hrs Possible])*DISTINCTCOUNT('KPI - Performance To Plan'[User Name])

This formula works for everyone except those that started working and submitting time after the model starts. It gives them the same amount of hrs as everyone else.

Actual Utilization = [Sum of Billable Hrs]/[Total Hrs Possible]

This actual utilization formula works fine, but once again only for those that have been in the system as long as the model goes.

I used @v-ljerr-msft's hrs calculation as below -

v-lijerr-Hours = SUMX('KPI - Performance To Plan',RELATED('Day'[Hrs Possible]))

@v-ljerr-msft's utilization formula -

v-lijerr-Utilization = DIVIDE(SUM('KPI - Performance To Plan'[Billable Hrs]),SUMX('KPI - Performance To Plan',RELATED('Day'[Hrs Possible])))    ---- This ended up being too low compared to what they should be when comapared

The below Matrices shows the difference in dates from before user - Tassone, Michael (4/1/2016) Starts and then after (3/15/17).

The "Total Hrs Possible is correct for everyone else because they have been their since before 4/1/2016. Tassone, Michael has not and should onyl have Total Hrs possible of around 100 or so hrs.

Ultimately the Total Hrs possible number should be same for eveyone except for those User Names that start later or after the time model begins

The below Screenshots show before and after User Name Tassone Michael Starts and how the it is bringind down the utilizaion number.

Once again thank you both @v-ljerr-msft and @spuder for helping me out with this. It has given me considerable trouble.

[v-ljerr-msft]

Hi @mtassone,

Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread? 

Regards

[mtassone]

Hey @v-ljerr-msft Im still having trouble calculating the Total Hrs Possible. Its not dynamic when it comes to users who begin after the the model begin in terms of time. The description of this problem is shown in the screenshot above. 

The above Matrices (Screenshots) shows the difference in dates from before user - Tassone, Michael (4/1/2016) Starts and then after (3/15/17).

The "Total Hrs Possible is correct for everyone else because they have been their since before 4/1/2016. Tassone, Michael has not and should only have Total Hrs possible of around 100 or so hrs.

Ultimately the Total Hrs possible number should be same for eveyone except for those User Names that start later or after the time model begins.

The current formual is only correct within certain time frames. For instance as long as the usernames have had records starting at the begining of the model (4/1/2016) the total hrs are correct. But if the user name starts at a later date (Tassone, Michael) the formula gives them the same amount of Total Hrs Possibel as everyone else and will bring their utilization down, thus bringing down the department utilization.

[mtassone]

@v-ljerr-msft

(4/1/16) ->(3/15/17) Screen Shot is later and includes Tassone Michael - SHould only have maybe a hundred hours when compared to everyone else's 1928.  (4/1/17) -> (2/17/17) screenshot is earlier and Tassone, Michael is not there becasue he has not started working yet. He should not have as many hours as everyone else in the (3/15/17) model becasue he has not had as many hours possible. The formual needs to be more dynamic so that as soon as his first record in Performance to plan Entry date begins - its starts adding his hours posisble in Date.