Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
Employee

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.