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

How to display the graph in percentage for daily, monthly, quarterly and yearly

I have Employee data and I need to show the Employees Utilization daily, monthly, quarterly and yearly in percentage. Also each employee work 8hrs/day

resource_utilization.jpg

 

 

 

 

AS shown in the above screenshot I have divided the "hours" column by 8 and converted to percentage as shown in "Utilization" column. so for every single day the graph is proper but when I select the month, the percentage is shown completely wron

1 ACCEPTED SOLUTION

@lavina03

 

According to the video on data strucure you provided above, I find that your table pattern with a loop like below:

candmrel_crossfilterwithloops.png

If you have a table pattern like this, with loops, then cross filtering can create an ambiguous set of relationships. So first of all, I would suggest you to change your table pattern, as cross filtering works well for a pattern of table relationships that look like the pattern below(star schema).

candmrel_crossfilterstarschema.png

Back to your original question, the formula below is for your reference:

Billable Hours = SUM ( Billable[Billable] ) / ( COUNTROWS ( Billable ) * 8 )

Reference:

Create and manage relationships in Power BI Desktop

 

Regards

View solution in original post

6 REPLIES 6
v-ljerr-msft
Microsoft
Microsoft

@lavina03



I have Employee data and I need to show the Employees Utilization daily, monthly, quarterly and yearly in percentage. Also each employee work 8hrs/day


In order to show the Employees Utilization daily, monthly, quarterly, and yearly in percentage, we may need to create a measure like below:

Employees Utilization = SUM(Hours)/(WorkingDays*8)

We need to know how many days employees work monthlyquarterly, and yearly. However, only from the table you provided above it may be difficult for me to figure out that.

 

So now you can try to create the measure yourself first. If you still have any question with that, please post your table structures and more sample data.

 

Regards

Hi @v-ljerr-msft,

 

Thank You for your reply.

 

I have created a video on data struture and visualization.

http://screencast.com/t/Am4t3DvIiW

 

Also I have attached the same data stucture tables:

 

https://docs.google.com/spreadsheets/d/1vprXSqM0HqHMvm-Uht1jsY73fFzP8ZTwDBBycffQeOI/edit?usp=sharing

 

 

@lavina03

 

According to the video on data strucure you provided above, I find that your table pattern with a loop like below:

candmrel_crossfilterwithloops.png

If you have a table pattern like this, with loops, then cross filtering can create an ambiguous set of relationships. So first of all, I would suggest you to change your table pattern, as cross filtering works well for a pattern of table relationships that look like the pattern below(star schema).

candmrel_crossfilterstarschema.png

Back to your original question, the formula below is for your reference:

Billable Hours = SUM ( Billable[Billable] ) / ( COUNTROWS ( Billable ) * 8 )

Reference:

Create and manage relationships in Power BI Desktop

 

Regards

@v-ljerr-msft,

 

Thank You, I figured out the formula. Instead of taking Countrows I used Count date and it worked.

 

Thanks @v-ljerr-msft

Your content is very informative

Please see video below

Expression and speech training

http://mahanteymouri.ir/expression-art/

@v-ljerr-msft,

 

I have modified the table pattern and made it simple. Also I used the formula using new measure, but the monthly percentage is still not 100% when I try to use that measure. Looks like it sums up.

 

http://screencast.com/t/URjumqi0hN00

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors