Forum Discussion

Omega's avatar
Omega
Impactful Individual
8 years ago
Solved

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:

 

MonthRegionCountrySales
JAN 2017DA10.00
JAN 2017DB20.00
JAN 2017EC30.00
FEB 2017DA30.00
FEB 2017DB50.00
FEB 2017EC90.00

 

 

New Table:

 

MonthRegionCountrySales
JAN 2017DA10.00
JAN 2017DB20.00
JAN 2017EC30.00
FEB 2017DA30.00
FEB 2017DB50.00
FEB 2017EC90.00
YTDDA40.00
YTDDB70.00
YTDEC120.00

 

 

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Omega
      Impactful Individual
      8 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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