cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GeorgesKV Regular Visitor
Regular Visitor

Calculate % divide by a fixed monthly number

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"

Chart that I need

User Date Billable Hours

Employee A2018-10-12Yes4
Employee A2018-10-12No3
Employee A2018-10-12No1
Employee A2018-10-13Yes2
1 ACCEPTED SOLUTION

Accepted Solutions
chirayuw Member
Member

Re: Calculate % divide by a fixed monthly number

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

12 REPLIES 12
chirayuw Member
Member

Re: Calculate % divide by a fixed monthly number

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 Regular Visitor
Regular Visitor

Re: Calculate % divide by a fixed monthly number

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.

chirayuw Member
Member

Re: Calculate % divide by a fixed monthly number

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 Regular Visitor
Regular Visitor

Re: Calculate % divide by a fixed monthly number

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,

Highlighted
chirayuw Member
Member

Re: Calculate % divide by a fixed monthly number

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 Regular Visitor
Regular Visitor

Re: Calculate % divide by a fixed monthly number

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

image.png

chirayuw Member
Member

Re: Calculate % divide by a fixed monthly number

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

GeorgesKV Regular Visitor
Regular Visitor

Re: Calculate % divide by a fixed monthly number

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!

image.png

chirayuw Member
Member

Re: Calculate % divide by a fixed monthly number

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 270 members 2,833 guests
Please welcome our newest community members: