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

Power Bi showing 10.8% instead of 110.8%!

Hey all,

So I have a calendar table with all the days and another column that has a DAX function doing this ( if is holiday or weekend equals 0 else 8), 8 is the standard daily work hours we have here in the company, this is working as expected, this column is called standard hours.

Aside from that, I have a table with all the employee and the hours they worked etc.

I've created a quick measure that is "sum of work hours divided by standard hours" in %, pretty simple right? But although the calculation is right, the visualization doesn't work as expected, see the image below:

image.png

So, this employee, worked 178 hours in April, in our company the standard is 5 days per week, 8 hours per day, so the math is:

5x8= 40 hours per week
4x40= 160 hours per month

So he worked 178 hours instead of the standard 160 hours for that month, this should be 110.8% (111% rounding up) but PBI is showing 10.8%.

I don't know what I'm doing wrong here.

Any thoughs?

9 REPLIES 9
Highlighted

Quick Measures are not too efficient and smart sometimes. Show us the measure created by quick measure

Highlighted

Total Ult. % =
DIVIDE(SUM('Hours Total'[Time (Hours)]), SUM('Calendar'[DayHours]))

Here you go, @rafaelmpsantos

Highlighted

Well, the measure is ok.

but try this

Total Ult. % =
DIVIDE(SUM('Hours Total'[Time (Hours)]), SUM('Calendar'[DayHours]))+1

Highlighted

@rafaelmpsantos the displayed numbers are right now but it broke the chart:

image.png

 

 


And if I try to do the same with "billable %", the results are very wrong, right now it should be 3% instead of 0.3% with the +1 it goes to 103%.

Highlighted

Use an if statement, if ( total hours > requested hours, your current calculation + 1, your current calculation)
Highlighted

@Xvierlh I'm sorry, should I add this to the measure?

Highlighted

Yes,

Currently i am posting on mobile please check for any missing parenthesis or typo.

Total Ult. % =
If(SUM('Hours Total'[Time (Hours)])>SUM('Calendar'[DayHours]), DIVIDE(SUM('Hours Total'[Time (Hours)]), SUM('Calendar'[DayHours]))+1, DIVIDE(SUM('Hours Total'[Time (Hours)]), SUM('Calendar'[DayHours])))
Highlighted

Thanks @Xvierlh but nothing changes, the DAX worked but it remained the same %.

Highlighted

Hi @GeorgesKV,

 

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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