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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.