Forum Discussion

nicolasvc's avatar
nicolasvc
Icon for Helper III rankHelper III
5 years ago
Solved

Create table with sum of columns

I have two tables, and each one shows sales of a store by year, month and week

storeyearmonthweeksales
A2021112500
A2021121000
...............
A202112532000

 

 

storeyearmonthweeksales
B202111500
B202112100
...............
B20211253800

 

And I need to have a table with the same date columns but that has the sum of each sales.

 

yearmonthweekvalue
2021113000
2021121100
............
202112532800

 

I tried to have a measure but for the sales of a store it gave me the total, and I think I will need to save the data, so the table suits me, how can I create it?

data protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    5 years ago

    nicolasvc 
    If you can append the two tables in Power Query, then you will have a single table. Add all the fields including the Sales column in a table visual except the Store , you should get the total as you need.

    You can also create a new table in Power BI using the code below and do the same if you don't want to do it in Power Query.

    New Table = UNION ( Table1 , Table2 )



     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

    New Table =
    VAR newtablecalendar =
    SUMMARIZE ( TableA, TableA[Year], TableA[Month], TableA[Week of Year] )
    VAR add_sumcolumn =
    ADDCOLUMNS (
    newtablecalendar,
    "@total_value",
    CALCULATE (
    VAR currentyear =
    VALUES ( TableA[Year] )
    VAR currentmonth =
    VALUES ( TableA[Month] )
    VAR currentweek =
    VALUES ( TableA[Week of Year] )
    VAR slicertable =
    CROSSJOIN ( currentyear, currentmonth, currentweek )
    RETURN
    CALCULATE (
    SUMX ( TableA, TableA[Sales] ),
    TREATAS ( slicertable, TableA[Year], TableA[Month], TableA[Week of Year] )
    )
    + CALCULATE (
    SUMX ( TableB, TableB[Sales] ),
    TREATAS ( slicertable, TableB[Year], TableB[Month], TableB[Week of Year] )
    )
    )
    )
    RETURN
    add_sumcolumn
     

     

     

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    nicolasvc 
    If you can append the two tables in Power Query, then you will have a single table. Add all the fields including the Sales column in a table visual except the Store , you should get the total as you need.

    You can also create a new table in Power BI using the code below and do the same if you don't want to do it in Power Query.

    New Table = UNION ( Table1 , Table2 )



     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

    New Table =
    VAR newtablecalendar =
    SUMMARIZE ( TableA, TableA[Year], TableA[Month], TableA[Week of Year] )
    VAR add_sumcolumn =
    ADDCOLUMNS (
    newtablecalendar,
    "@total_value",
    CALCULATE (
    VAR currentyear =
    VALUES ( TableA[Year] )
    VAR currentmonth =
    VALUES ( TableA[Month] )
    VAR currentweek =
    VALUES ( TableA[Week of Year] )
    VAR slicertable =
    CROSSJOIN ( currentyear, currentmonth, currentweek )
    RETURN
    CALCULATE (
    SUMX ( TableA, TableA[Sales] ),
    TREATAS ( slicertable, TableA[Year], TableA[Month], TableA[Week of Year] )
    )
    + CALCULATE (
    SUMX ( TableB, TableB[Sales] ),
    TREATAS ( slicertable, TableB[Year], TableB[Month], TableB[Week of Year] )
    )
    )
    )
    RETURN
    add_sumcolumn