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
jereaallikko
Helper III
Helper III

How to show utilization percentage progress over time

Hi all,

 

I am trying to make a chart showing the progress of percentage showing utilization of test environment (how many hours the environment has been used compared to the available hours).

I have a Table showing Test Date, Test Duration, Test Name. In addition I have created a date table.

 

I made a DAX calculating total hours from the beginning of the year like this:

HoursOfYear = DATEDIFF(DATE(2020;1;1);TODAY();DAY)*24
 
And then used the result to calculate the utilization percentage like this:
%Utilization = DIVIDE(SUM('Sheet1 (2)'[Test Duration]);[HoursOfYear])
 
Capture1.PNG
 
 
 
 
 
 
 
 
 
 
 
Works well in a Gauge visual like this.
 
But I would like to get it into a visual like this:
Capture2.PNG
 
 
 
 
With the measure I made it doesn't work.
Can anyone help me with the issue? Would appreciate it. Feel free to ask more info about the data if needed.
Thanks.
 
BR, Jere Aallikko
1 ACCEPTED SOLUTION

@jereaallikko 

The measure is correct. You just need to create a 1-to-many relationship between DateTable[Date] and Sheet1 (2)[Date]

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

By considering “how many hours the environment has been used compared to the available hours”

If you create a measure which will calculate used hours for the Month

And another measure will calculate available hours for that month

Then you will able to get utilization % for the test

Day utilization:

Consider, Test A utilization %

Test Date: 23-10-2020

Test Duration: 4 hrs

By considering 8hrs on daily basis is the available hours

Then Test A utilization % for 23-10-2020 will be 4/8 = 50%

Month Utilization:

Now if you want to calculate for Oct Month

Then create a measure which will calculate sum of used hours for test using Test Duration column

Test Duration Month-wise = sum(Testduration)

In Month-wise calculation you will require Working days for the each month table

So Available Hours = working day for month * 8

For example, in Oct working days are 22 and daily available hours are 8

Then Available hours = 22*8 =>176

For Test A test duration total for the month is 150

Then Utilization of Test A for Oct month will be (150/176)*100 => 85.22%

If this post is helpful please like the post

@jereaallikko 

Can you share the pbix (or a mock version that reproduces the issue)? It would then be easier to come up with an accurate solution

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Hi @AlB 

 

Cannot share the pbix file for some reason, but here is the drive link for a mock version of the report and its data.

https://drive.google.com/file/d/1k25MW6BRwKKNy1qxtfCDqzDCEK8cHgPx/view?usp=sharing

Let me know if more information is needed.

 

Thanks,

Jere

@jereaallikko 

The measure is correct. You just need to create a 1-to-many relationship between DateTable[Date] and Sheet1 (2)[Date]

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

AlB
Super User
Super User

Hi @jereaallikko 

Try

%Utilization =
CALCULATE (
    DIVIDE ( SUM ( 'Sheet1 (2)'[Test Duration] ), [HoursOfYear] ),
    FILTER ( ALL ( DateTable[Date] ), DateTable[Date] <= MAX ( DateTable[Date] ) )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

Hi @AlB 

 

I tried it and it shows the correct percentage, but when applying it to a chart, the line value (measure) does not change along the time:

jereaallikko_1-1603432898560.png

 

What am I doing wrong?

 

BR,

Jere

 

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.

Top Solution Authors