Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I made total working days in a month in calendar table. Now I need to use this data to calculate total hours of work delivered in main table based on if a condition is satisifed or not.
FTE of Rate Type Onshore =
(
IF (
[Rate Type] == "Paid",
(
IF (
[office] == "Farm",
[Hours delivered] / 157.08 ,
(
IF (
[office] == "Boult",
[Hours delivered] /127.68 ,
(
IF (
[office] == "Global",
[Hours delivered] / 136.92,
(
IF (
[office] == "GOTS",
[Hours delivered] / 131.04,
(
IF (
[office] == "Daxnd",
[Hours delivered] / 136.92,
(
IF (
[office] == "UK",
[Hours delivered] / 131.04,
(
IF (
[office] == "UTS",
[Hours delivered] / 131.04,
(
IF ( [office] == "ANZ", [Hours delivered] / 149.94, 0 )
)))))))))))))))))
As the values with which I'm dividing "Hours delieverd " is for month of Aug, but I want to change it dynamically with every month. I tried creating a measure(not able to create column) and dividing, but it gives me wrong values.
I'm not sure how to use no. of working days or hours in a month to compute final result.
I have a calendar table with working days in it but not sure how to get what I want.
Thanks in advance.
EDIT 1:- I forgot to mention how I got working hours(157.08) in the first place. So, it's like (otal no. of working hours in a day * no. of working days in a month), total no. of working hours in a day is fixed.
EDIT 2 :- @AllisonKennedy , @amitchandak can you guys help?
Hi,
The issue-
Data available -
Fact table has more than 70columns and 200000rows out of which the table I need to create in BI is concerened with only Service Period, Rate Type, LSA , Type of work, charge type and no. of hours delieverd.
Example-
LSA | Service Period | Rate Type | Hours Delieverd | Type of work | Charge type |
LSA1 | 01-07-2014 | Onshore | 1 | Service for assistance | Fixed |
LSA2 | 01-12-2015 | Onshore | 0.5 | Project | Fixed |
LSA3 | 01-06-2019 | Offshore | 2 | Project | Variable |
LSA2 | 01-09-2020 | Onshore | 12 | Enhancement | Variable |
LSA4 | 01-06-2019 | Offshore | 2 | Maintenance | Fixed |
Desired Output-
I need to create a table which calculates the no. of hours delivered based on Rate Type, Charge Type, Type of Work on the basis of Month and Year and LSA.
Example- Where the data changes dynamically with month and year
LSA | FTE of Rate Type Onshore | FTE of Rate Type Offshore | FTE of Rate Type Blended | FTE for Application Enhancement | FTE of work type Variable |
LSA1 | 25.3 | 77.8 | 7.1 | 0.0 | 4.9 |
LSA2 | 26.4 | 1.6 | 23.8 | 2.6 | 61.1 |
LSA3 | 4.0 | 0.9 | 68.6 | 0.0 | 0.0 |
LSA4 | 1.3 | 46.2 | 20.9 | 0.0 | 23.7 |
Now these values are calculated using-
No. of hours of work delivered in a month(with conditions) divided by total no. of available working hours in a month. which is different for different LSAs. Example-
Total working hours in a month = no. of working hours in a dayfixed but different for Onshore and Offshore) * no. of working days in a month(changes dynamically)
LSA1(working hours in a month for LSA1) = 6.24 * 21(131.04) OR,
LSA2(working hours in a month for LSA2) = 6.52* 21(136.92)
What has be done so far-
Completed the table and logic except the final calculation part(wrong values I got).
Thing where I'm stuck-
I have devised logic for calculating no. of working days (a measure in fact table), using calendar table, but when I'm using it to divide it with sum of professional hours delivered. It does not produce an expected output.
The code I'm using is given above.
Everything is working perfectly fine sperately, but when I'm using measure for division the output produced is wrong.
Help would be much appreciated I'm stuck here for last 2 days(just a beginner).
Thanks in advance for helping.
@Anonymous Where does the 131.04, 136.92, etc come from?
Please remind me what your Fact (main table) looks like.
You should be able to create a measure for the denominator:
[Working Hours] = SUMX(Date, Date[Working Days]*8)
Where 8 is the fixed number of working hours in a day.
As long as you use the Date[Date] in the visual, you should have no trouble using that [Working Hours] measure in the denominator of your measure calculation.
You also may consider using SWITCH instead of so many nested IF statements.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy I tried creating measure as you suggested, but it's not working correctly. It is producing same output as before.
131.04 = 6.08(working hours in a day) * 21(August 2020 -21 working days)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |