Creating YTD Table and append in original table

[Omega]

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

[v-huizhn-msft · Accepted solution]

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

[Omega]

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!

[v-huizhn-msft]

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

[Omega]

Thanks!