Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create new row based on previous row and sum the values

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

 

Need Help
Tips and Tricks
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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