Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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
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
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
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
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
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
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
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |