Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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)
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.
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
@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,
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
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,
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
I don't know where I went wrong, it isn't showing by month but all the dates.
Take the MonthName column from the sample file in the Main table
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!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |