Add to a "Summarize" Table

[vrossouw]

Hi, I have created a new table with the "Summarize" function. 

New Table = SUMMARIZE(Sales,Sales[Part Number],"Total Sales per Item",

CALCULATE(Sales[Sales Value]))

How can I also bring in the Year column from the Sales Table?  Seems as if the summarize function only accept measures. The year column is in a text type format.

There is a relationship between the New Table and Sales Table.  If I attempt to add the year to the New Table, link column, which is 'part number', change to multiple rows in the New Table.

Then the relationship change to 'many to many' and the data no longer makes sense.  I want to filter on the year, and both visuals, from both tables, must be interactive.

 Sales Table

Part Number	Sales Value	Year
A90555A	545326	2019
A90555A	437381	2020
A90555A	568292	2021
C90558A	556003	2019
C90558A	498343	2020
C90558A	301223	2021
E90557A	831683	2019
E90557A	839977	2020
E90557A	475563	2021

New Table  (How do I bring the year into this table). Related function does not seem to work.

Part Number	Total Sales per Item
A90555A	1550999
C90558A	1355569
E90557A	2147223

 Thanks in advance

[v-alq-msft · Accepted solution]

Hi, @vrossouw 

Based on your description, it is available to create a calculated table as follows.

New = SUMMARIZE(

    Sales,

    Sales[Part Number],

    Sales[Year],

    "Total Sales per Item",

    CALCULATE(SUM(Sales[Sales Value])

))

While the result of summarize function is the same as the sales table. If you want to repeat a table, you can click ‘Edit Query’, go to Query Editor, right-click the sales query and choose ‘Duplicate’ to get a same table.

If you want to filter on the year, you can use ‘Part Number’, ‘Sales Value’ in a table visual and use ‘Year’ as the slicer to filter it.

If I misunderstand your thought, please show me your expected output. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @vrossouw,

New Table = SUMMARIZE(Sales,Sales[Part Number],Sales[Year],"Total Sales per Item and Year",
CALCULATE(Sales[Sales Value]))

does it make sense what you want to do?

Why is there a relationship between the New Table and Sales Table?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.