Add the value from one article to other articles

[Anonymous]

I have a table with Articles that have a combination of two articles. I want to create a new Value Column where I add the value of those articles to the basic one.

Example: The value from article Vitamin C + Vitamin D needs to be added +20 on Vitamin C and +20 on Vitamin D. I'm not sure with is easier in dax or in query.

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to attached sample file with the proposed solution based on measure.

DAX Value = 
SUMX ( 
    VALUES ( 'Table'[Article] ),
    VAR CurrentArticle = 'Table'[Article]
    RETURN
        IF ( 
            CONTAINSSTRING ( CurrentArticle, "+" ),
            0,
            SUMX ( 
                FILTER ( 
                    ALL ( 'Table'[Article] ),
                    CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
                ), 
                VAR ThisArticle = 'Table'[Article]
                RETURN
                    CALCULATE ( SUM ( 'Table'[Value] ) )
            )
        )
)

[Anonymous]

Thank you, @tamerj1 

Can you explain the logic behind it? How it knows that an Article that contains + needs to go on the corelated Article and not another? 

[tamerj1]

@Anonymous 
It doesn't have to do that. It just need to check whether the current row Article name is part of the name of the article under the iteration inside FILTER.

The code is pretty simple, just concentrate on the inner SUMX:

SUMX (
    FILTER (
        ALL ( 'Table'[Article] ),
        CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
    ),
    CALCULATE ( SUM ( 'Table'[Value] ) )
)

This is a simple SUMX over a filter of all articles that conatin the name of the current article.

The if statement is just to return zoro in case "+" is partr of the name (a combined name)

IF (
    CONTAINSSTRING ( CurrentArticle, "+" ),
    0,
    SUMX (
        FILTER (
            ALL ( 'Table'[Article] ),
            CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
        ),
        CALCULATE ( SUM ( 'Table'[Value] ) )
    )
)

And finally the outer SUMX is just to provide correct values at total level

DAX Value =
SUMX (
    VALUES ( 'Table'[Article] ),
    VAR CurrentArticle = 'Table'[Article]
    RETURN
        IF (
            CONTAINSSTRING ( CurrentArticle, "+" ),
            0,
            SUMX (
                FILTER (
                    ALL ( 'Table'[Article] ),
                    CONTAINSSTRING ( 'Table'[Article], CurrentArticle )
                ),
                CALCULATE ( SUM ( 'Table'[Value] ) )
            )
        )
)

[Anonymous]

Thank you!

That was a sample made by my because I thought it is more difficult.

For this kind of articles do you think there is a soulution?

On the left are the base article and on the right are the combined articles.

Cod Base	Name Base	Cod Pack	Name Pack
12892	Alive! Men’s 50+ Ultra 30 tab filmate	P12892	Alive! Men’s 50+ + Alive! Women’s 50+ Ultra 30tab
12893	Alive! Women’s 50+ Ultra 30 tab filmate	P12892	Alive! Men’s 50+ + Alive! Women’s 50+ Ultra 30tab
12894	Alive! Men’s Ultra 30 tab filmate	P12894	Alive! Men’s +Women`s Ultra 30 tab filmate
12895	Alive! Women’s Ultra 30 tab filmate	P12894	Alive! Men’s +Women`s Ultra 30 tab filmate
11852	Vitamin D3 2000UI (adulti) 30 caps moi	P31501	Vitamin C adulti + Vitamin D3 adulti 30cps
31501	Vitamin C 1000mg (adulti) 30 caps veg	P31501	Vitamin C adulti + Vitamin D3 adulti 30cps
47239	L-Arginine 1000mg 30 tab RapidSolv	PP47239	L-Arginine 1000mg 30 tab+Guard-Your-Liver 30 caps
200005	Guard-Your-Liver	PP47239	L-Arginine 1000mg 30 tab+Guard-Your-Liver 30 caps
862	MSM 750mg 90 caps veg	PP862	MSM 750mg + Vitamin D3 30cps GRATIS
11852	Vitamin D3 30cps	PP862	MSM 750mg + Vitamin D3 30cps GRATIS
CL10150	First Defense	PPCL10150	First Defense + Vitamina C copii
CL10200	Vitamin C 250mg (copii) 118ml	PPCL10150	First Defense + Vitamina C copii
CL10200	Vitamin C 250mg (copii) 118ml	PPCL10200	Vitamin C adulti 30cps + Vitamin C copii 250mg
31501	Vitamin C 1000mg (adulti) 30 caps veg	PPCL10200	Vitamin C adulti 30cps + Vitamin C copii 250mg
CL10900	Vitamin D3 500UI (copii) 30ml	PPCL10900	Vitamin D3 adulti 30cps + Vitamin D3 copii 30ml
11852	Vitamin D3 2000UI (adulti) 30 caps moi	PPCL10900	Vitamin D3 adulti 30cps + Vitamin D3 copii 30ml
862	MSM 750mg 90 caps veg	PR862	MSM 750mg + Vitamin D3 30cps
11852	Vitamin D3 30cps	PR862	MSM 750mg + Vitamin D3 30cps
CL10150	First Defense 118.50ml	PRPCL10150	First Defense 118.50ml + Toothpaste Tablets 60 tablete (gust de fructe) gratuit
CL10200	Vitamin C 250mg (copii) 118ml	PRPCL10200	Kit imunitate copii (Vitamin C+ D3)
CL10900	Vitamin D3 500UI (copii) 30ml	PRPCL10200	Kit imunitate copii (Vitamin C+ D3)

[tamerj1]

@Anonymous 
Where is the value and what is the expected result based on this sample data?

[Anonymous]

sorry, this is

Cod Base	Name Base	Cod Pack	Name Pack	Value	Expected Result
12892	Alive! Men’s 50+ Ultra 30 tab filmate	P12892	Alive! Men’s 50+ + Alive! Women’s 50+ Ultra 30tab	40	40
12893	Alive! Women’s 50+ Ultra 30 tab filmate	P12892	Alive! Men’s 50+ + Alive! Women’s 50+ Ultra 30tab		40
12894	Alive! Men’s Ultra 30 tab filmate	P12894	Alive! Men’s +Women`s Ultra 30 tab filmate	40	40
12895	Alive! Women’s Ultra 30 tab filmate	P12894	Alive! Men’s +Women`s Ultra 30 tab filmate		40
11852	Vitamin D3 2000UI (adulti) 30 caps moi	P31501	Vitamin C adulti + Vitamin D3 adulti 30cps	40	40
31501	Vitamin C 1000mg (adulti) 30 caps veg	P31501	Vitamin C adulti + Vitamin D3 adulti 30cps		40
47239	L-Arginine 1000mg 30 tab RapidSolv	PP47239	L-Arginine 1000mg 30 tab+Guard-Your-Liver 30 caps	40	40
200005	Guard-Your-Liver	PP47239	L-Arginine 1000mg 30 tab+Guard-Your-Liver 30 caps		40
862	MSM 750mg 90 caps veg	PP862	MSM 750mg + Vitamin D3 30cps GRATIS	40	40
11852	Vitamin D3 30cps	PP862	MSM 750mg + Vitamin D3 30cps GRATIS		40
CL10150	First Defense	PPCL10150	First Defense + Vitamina C copii	40	40
CL10200	Vitamin C 250mg (copii) 118ml	PPCL10150	First Defense + Vitamina C copii		40
CL10200	Vitamin C 250mg (copii) 118ml	PPCL10200	Vitamin C adulti 30cps + Vitamin C copii 250mg	40	40
31501	Vitamin C 1000mg (adulti) 30 caps veg	PPCL10200	Vitamin C adulti 30cps + Vitamin C copii 250mg		40
CL10900	Vitamin D3 500UI (copii) 30ml	PPCL10900	Vitamin D3 adulti 30cps + Vitamin D3 copii 30ml	40	40
11852	Vitamin D3 2000UI (adulti) 30 caps moi	PPCL10900	Vitamin D3 adulti 30cps + Vitamin D3 copii 30ml		40
862	MSM 750mg 90 caps veg	PR862	MSM 750mg + Vitamin D3 30cps	40	40
11852	Vitamin D3 30cps	PR862	MSM 750mg + Vitamin D3 30cps		40
CL10200	Vitamin C 250mg (copii) 118ml	PRPCL10200	Kit imunitate copii (Vitamin C+ D3)	40	40
CL10900	Vitamin D3 500UI (copii) 30ml	PRPCL10200	Kit imunitate copii (Vitamin C+ D3)		40

[tamerj1]

@Anonymous 

Again I don't think this reflects the real problem. If this really represents the real case then the solution is simply 

Expected Result (Cslculated Colunn) =
CALCULATE (

SUM ( 'Table'[Value] ),

ALLEXCEPT ( 'Table', 'Table'[Cod Pack] )

)