cancel
Showing results for
Did you mean:
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"

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
1 ACCEPTED SOLUTION

Accepted Solutions
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
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)```
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.

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

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,

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

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.

Member

## Re: Calculate % divide by a fixed monthly number

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

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!

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

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 28 members 803 guests
Recent signins: