Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi! Need help please on the following calculation that I need to translate to PBI. Here's what I got in Excel.
I need to be able to compute for the overall avg hours work per site and account using the formulas noted on the screenshot.
Thanks for all the help!
Solved! Go to Solution.
Hi @minapot,
Try this:
Avg = IF ( HASONEVALUE ( table[emp_id] ), DIVIDE ( SUM ( table[total hrs worked] ), COUNT ( table[wknum] ) ), DIVIDE ( DIVIDE ( COUNT ( table[wknum] ), [measure to divide] ), DISTINCTCOUNT ( table[emp_id] ) ) )
Replace the text in red with the appropriate measure since you did not mention which column to divide the count of weeks by for the average.
Proud to be a Super User!
Hello,
after loading your data to DataModel you can create the following measures:
CountOfWeeks=Count(Table[wknum])
Total_hrs=Sum(Table[total hrs worked])
avg_hrs=[Total_hrs]/[CountOfWeeks]
Best regards.
Hi Floriankx! Thanks for your reply! I tried your suggestions but failed to get the correct output.
For the overall avg hrs worked, I need to have the total worked hrs for the site/account and then divide that by the average number of weeks the employees for that site/account worked and then divide the answer to the distinct # of employees for that site/account.
so... site/account avg hrs worked = (total hours worked per site/ave # of weeks with schedule)/distinctcount(HC)
Hi @minapot,
Try this:
Avg = IF ( HASONEVALUE ( table[emp_id] ), DIVIDE ( SUM ( table[total hrs worked] ), COUNT ( table[wknum] ) ), DIVIDE ( DIVIDE ( COUNT ( table[wknum] ), [measure to divide] ), DISTINCTCOUNT ( table[emp_id] ) ) )
Replace the text in red with the appropriate measure since you did not mention which column to divide the count of weeks by for the average.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |