cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
12 REPLIES 12
Highlighted

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.

Highlighted

@chirayuw 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,

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors