cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Impactful Individual

## Creating YTD Table and append in original table

Hi,

I have the below table which contains the sales per country, region and month. I am trying to create YTD table that will calculate the total sum for each country and then add the new table to the orignial table.

Is that possible?

Old Table:

 Month Region Country Sales JAN 2017 D A 10.00 JAN 2017 D B 20.00 JAN 2017 E C 30.00 FEB 2017 D A 30.00 FEB 2017 D B 50.00 FEB 2017 E C 90.00

New Table:

 Month Region Country Sales JAN 2017 D A 10.00 JAN 2017 D B 20.00 JAN 2017 E C 30.00 FEB 2017 D A 30.00 FEB 2017 D B 50.00 FEB 2017 E C 90.00 YTD D A 40.00 YTD D B 70.00 YTD E C 120.00

1 ACCEPTED SOLUTION
Microsoft Employee

Hi @Omega,

Yes, it is possible, please create a new table using the following formula by clicking "New Table" under Modeling on Home page. I assume your old table name is Table1. You can replace it to your table name.

```NewTable =
UNION (
Table1,
SUMMARIZE (
ADDCOLUMNS ( Table1, "YTD", "YTD" ),
[YTD],
Table1[Region],
Table1[Country],
"total", SUM ( Table1[Sales] )
)
)
```

You will get expected result as follows.

Best Regards,
Angelia

4 REPLIES 4
Microsoft Employee

Hi @Omega,

Yes, it is possible, please create a new table using the following formula by clicking "New Table" under Modeling on Home page. I assume your old table name is Table1. You can replace it to your table name.

```NewTable =
UNION (
Table1,
SUMMARIZE (
ADDCOLUMNS ( Table1, "YTD", "YTD" ),
[YTD],
Table1[Region],
Table1[Country],
"total", SUM ( Table1[Sales] )
)
)
```

You will get expected result as follows.

Best Regards,
Angelia

Impactful Individual

Thanks a lot @v-huizhn-msft

Question: If I have 5 more columns such as factory, city....etc. Do I have to add all 5 columns next to country in the new table formula?

Thanks!

Microsoft Employee

Hi @Omega,

You need to calculate the sum of sales group by region, country, factory, city and so on. We union two new tables in the formula, the number of columns must same if we use UNION function. And please check more details about SUMMARIZE function from here, you will totally understand the formula above.

Best Regards,
Angelia

Impactful Individual

Thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.