Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
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!
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
Thanks!