Sum multiple rows based on value (Sales of old vs new material numbers) Link to example file include

[TimvMechelen]

Hi all,

First of all, I have a material master table which displays if it's a "Old" or "New" material. An "Old" material means that there is a "New" article, which is a replacement for the old article. In some of the sales analysis, I would like to take into account the sales data of both the "Old" and "New" material, which adds the sales of the "Old" material number to the "New" material number. 
So from the table below, material "4" is a replacement for material "1". Material "6" and "7" do not have "Old" material numbers.

As said above, I'd like the possibility to create 2 views:

- One sales analysis with only the sales per article. This one is already correct in the file attached, see below.

 

 

- One sales analysis, which takes into account the sales data of both the "Old" and "New" material, which adds the sales of the "Old" material number to the "New" material number. The figure below is what I currently have in Power BI.

This is the table I'd like to see:

Material number	2022-06	2022-07	2022-08	Total
4	22	22	60	104
4 (new)	11	11	30	52
1 (old)	11	11	30	52
5	55	80	0	135
5 (new)	7	26	0	33
2 (old)	48	54	0	102
6	45	7	12	64
6 (new)	45	7	12	64
7	10	17	0	27
7 (new)	10	17	0	27
8	4	22	34	60
8 (new)	0	0	30	30
3 (old)	4	22	4	30

Is this possible? I tried many things, but nothing works...

Dropbox link to Power BI example file 

Any help with this would be greatly appreciated! 

[jgeddes]

If Greg's solution does not work for you, and it is possible to add columns to the Material Masterdata Table and then use those columns to get your desired result.
Add a master material number column that assigns the new material number to any old material numbers.

Master Material Number =

if(

    not(isblank(LOOKUPVALUE('Material Masterdata'[Old material number],'Material Masterdata'[Old material number],'Material Masterdata'[Material number]))),

    LOOKUPVALUE('Material Masterdata'[Material number],'Material Masterdata'[Old material number],'Material Masterdata'[Material number]),

    'Material Masterdata'[Material number]

)

Then add a formatted text column to show new versus old material numbers

Named Material Numbers =

[Material number] & "("& [Old/New material number] & ")"

Use those two columns in your visual

And you should end up with

[TimvMechelen]

@jgeddes

One more question. What if there are multiple new material numbers with the same old material number? Then the formula below doesn't work anymore. I get the following error: 

"A table of multiple values was supplied where a single value was expected."

Master Material Number =

if(

    not(isblank(LOOKUPVALUE('Material Masterdata'[Old material number],'Material Masterdata'[Old material number],'Material Masterdata'[Material number]))),

    LOOKUPVALUE('Material Masterdata'[Material number],'Material Masterdata'[Old material number],'Material Masterdata'[Material number]),

    'Material Masterdata'[Material number]

)

In this case I'd like not to look at the old material number. So in my sales analysis, I'd like to see only the sales of the new article number. How can I fix this?

Thank you in advance!

[TimvMechelen]

@jgeddes, thank you a lot for your reply. It works!

[Greg_Deckler]

@TimvMechelen Perhaps have your source table like:

Material	Old Material	Old or new
1		Old
4	1	Old
4	4	New
7	7	New