cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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:

Regards,

Arjun

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Create new row based on previous row and sum the values

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 =
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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Microsoft

## Re: Create new row based on previous row and sum the values

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 =
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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Anonymous
Not applicable

## Re: Create new row based on previous row and sum the values

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!