cancel
Showing results for
Did you mean:

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.

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

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors