How to show utilization percentage progress over time

[jereaallikko]

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])

Works well in a Gauge visual like this.

But I would like to get it into a visual like this:

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

[Anonymous]

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

[AlB]

@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 

[jereaallikko]

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

[AlB · 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 

[AlB]

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 

[jereaallikko]

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:

What am I doing wrong?

BR,

Jere