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 Everyone,
I'm striving to create report based on the below input:
Criteria:
For every Emp Id in table should create one new row (refering above row details like proj Code, Hrs Leaves and Actual Hrs) & it should sum Unbilled(if available) + Leaves columns of respective Emp in Tot Hrs column. At same time, status column should display with 0.
Below is out:
Could you please help me to acheive this.
Regards,
Arjun
Solved! Go to Solution.
Hi @Anonymous,
Please new calculated tables with below DAX formulas: (suppose source table is called 'Test3')
Test3_1 = SUMMARIZE ( SELECTCOLUMNS ( Test3, "Emp ID", Test3[Emp ID], "Proj Code", Test3[Proj Code], "Hrs", Test3[Hrs], "Leaves", Test3[Leaves], "Actual Hrs", Test3[Actual Hrs], "Tot Hrs", 0, "Unbilled", 0, "Status", 0 ), [Emp ID], [Proj Code], "Hrs", AVERAGE ( Test3[Hrs] ), "Leaves", AVERAGE ( Test3[Leaves] ), "Actual Hrs", AVERAGE ( Test3[Actual Hrs] ), "Tot Hrs", 0, "Unbilled", 0, "Status", 0 ) Test3_2 = UNION ( Test3, Test3_1 ) Test3_3 = ADDCOLUMNS ( SUMMARIZE ( Test3_2, Test3_2[Emp ID], Test3_2[Proj Code], Test3_2[Status], "Hrs", AVERAGE ( Test3_2[Hrs] ), "Leaves", AVERAGE ( Test3_2[Leaves] ), "Actual Hrs", AVERAGE ( Test3_2[Actual Hrs] ), "Tot Hrs", SUM ( Test3_2[Tot Hrs] ), "Unbilled", SUM ( Test3_2[Unbilled] ) ), "Final Tot Hours", IF ( Test3_2[Status] = 0, [Leaves] + [Unbilled], [Tot Hrs] ) )
Best regards,
Yuliana Gu
Hi @Anonymous,
Please new calculated tables with below DAX formulas: (suppose source table is called 'Test3')
Test3_1 = SUMMARIZE ( SELECTCOLUMNS ( Test3, "Emp ID", Test3[Emp ID], "Proj Code", Test3[Proj Code], "Hrs", Test3[Hrs], "Leaves", Test3[Leaves], "Actual Hrs", Test3[Actual Hrs], "Tot Hrs", 0, "Unbilled", 0, "Status", 0 ), [Emp ID], [Proj Code], "Hrs", AVERAGE ( Test3[Hrs] ), "Leaves", AVERAGE ( Test3[Leaves] ), "Actual Hrs", AVERAGE ( Test3[Actual Hrs] ), "Tot Hrs", 0, "Unbilled", 0, "Status", 0 ) Test3_2 = UNION ( Test3, Test3_1 ) Test3_3 = ADDCOLUMNS ( SUMMARIZE ( Test3_2, Test3_2[Emp ID], Test3_2[Proj Code], Test3_2[Status], "Hrs", AVERAGE ( Test3_2[Hrs] ), "Leaves", AVERAGE ( Test3_2[Leaves] ), "Actual Hrs", AVERAGE ( Test3_2[Actual Hrs] ), "Tot Hrs", SUM ( Test3_2[Tot Hrs] ), "Unbilled", SUM ( Test3_2[Unbilled] ) ), "Final Tot Hours", IF ( Test3_2[Status] = 0, [Leaves] + [Unbilled], [Tot Hrs] ) )
Best regards,
Yuliana Gu
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |