Calculate % divide by a fixed monthly number

[GeorgesKV]

Hi all,

So I have two columns as stated in the picture above, where I have a date and worked hours, the date repeats since you don't usually work 8 hours in one thing only. What I need is to achieve this chart (image below) so for the bars it's okay, I've already calculated billable hours, but now I don't know how to calculate the % based on what should be a month time of work (160 hours w/o holidays) to create the lines.  In excel I would just create a row that "=(A row with the monthly sum of hours)/160"

User Date Billable Hours

Employee A	2018-10-12	Yes	4
Employee A	2018-10-12	No	3
Employee A	2018-10-12	No	1
Employee A	2018-10-13	Yes	2

[Anonymous]

Assuming that the No refers to "Not Billable" so these get excluded from the calculation?

try this for measure

BillablePercent = 
VAR
BillAMt = 
CALCULATE(SUM(Table1[Hours]),FILTER(Table1,Table1[Billable]="Yes"))

VAR
AllAmt = 
CALCULATE(SUM(Table1[Hours]))

Return
DIVIDE(BillAMt,AllAmt)

[GeorgesKV]

No, they are calculated differently, I already have a column that only mirrors the hours where there is a "yes" in billable, my problem is to create the line presented in that chart I've posted, I need to sum the hours monthly and divide by 160.

And it would be great if I can change this 160 when there's a holiday in the month to reflect it.

[Anonymous · Accepted solution]

Do you calculate month as 20 working days? or what basis is 160 calculated

as in number of days and no of hours per day

created sample file. have a look

https://ufile.io/o7yoi

[GeorgesKV]

@Anonymous is it possible to make a filter in that DAX you sent me in the sample for Holiday where instead of 0 it counts as 4?

Regards,

[Anonymous]

Hi, Edited the formula in the Calendar Table to say 4 for Sat/Sun/ Holiday. Or do you want Sat + Sun as 0 but Holiday as 4

Also I've almost redone the file because I was trying to fix the issue of not going above 100%

Have a look and let me know if this is what u need

https://ufile.io/jc0o0

[GeorgesKV]

20 working days, 8 hours per day. 

I think this is getting closer to what I need, is the line considering weekend, because to show real value it needs to be 20x8( - holidays). 

So what I need now is to create an excel with all the holidays?

Regards,

[Anonymous]

Yeah if you look at the table view there are 3 tables I've used. 1 is the Main table that had the Employee data, 1 is the Holiday table which exclusively has Dates and Holiday names. The last table is the calendar table where the hours per day are calculated.

So if you edit the "DayHours" column and change the 7 in the formula to an 8 it should give you what you need

It calculates as Mon-Fri is 8 hours unless theres a holiday on that day. Sat + Sun + Holidays = 0 hours

[GeorgesKV]

I don't know where I went wrong, it isn't showing by month but all the dates.

[Anonymous]

Take the MonthName column from the sample file in the Main table

[GeorgesKV]

Yes, I've managed to do that, I'm really close but I don't know where is the last thing I'm missing, as you can see in the picture below, visual is identical but in one (the right one) it's showing 104% since that employee worked 4% more than 160 hours (w/o holidays) but in power bi it's showing 10.4% I don't know why!

[Anonymous]

Not sure without being able to see the actual data. Because when you boil the calculation down, all its doing is summing the "Yes" by Month/Year then dividing it by the Hours by Month/Year

[GeorgesKV]

The measure is doing this:

Excel: 165/160 = 105%
PBI: 165/160 = 10.4%

The problem is that Power Bi is showing 10.4 instead of 104% and I don't know why.