Create new row based on previous row and sum the values

[Anonymous]

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

[v-yulgu-msft · Accepted 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

[Anonymous]

Thank you @v-yulgu-msft, for the solution.