What function should I use to pass a measure result from one item to another

[KARIM-K]

Hi All,

I need help please! I have built a data model in powerpivot to calculate the industrial losses by comparing the real quantity consumption to the theoretic BOM consumption based on the volume produced.

The data model looks working fine until I get stuck with the following issue: How to pass the theoretic consumption Quantity from Item 2 to Item 1 to obtain the following result:

- Item 1 quantity consumption = its initial Quantity + 1,8 x Quantity consumtion Item 2

- Item 2 quantity consumtion becomes 0

- All other items we keep the same quantity consumption unchanged

I have made many trials to overcome this issue but without success. I have tried SWITCH, IF function but I didn't succeed to get around it.

To showcase this issue, I have created the following dataset:

Item CodeItemQuantityUnit PriceSales

Item Code	Item	Quantity	Unit Price	Sales
1	Item 1	100	2	200
2	Item 2	50	3	150
3	Item 3	20	4	80
4	Item 4	10	2	20
5	Item 5	5	1	5

The idea is to end up having the following result:

Item Code	Item	Quantity	Unit Price	Sales	Sales CONV
1	Item 1	100	2	200	350
2	Item 2	50	3	150	0
3	Item 3	20	4	80	80
4	Item 4	10	2	20	20
5	Item 5	5	1	5	5
TOTAL				455	455

Here are some of the trials I have made: 

Item Code	SALES FY	SALES FY ITEM 1	SALES FY ITEM 2	SALES FY CONV
Item 1	200	200		200
Item 2	150	150	150	0
Item 3	80			80
Item 4	20			20
Item 5	5			5
TOTAL	455	350	150	350

SALES FY = SUM(Tableau1[Sales])

SALES FY ITEM 1= CALCULATE(SUMX(Tableau1; [Sales]); FILTER(Tableau1; OR([Item Code]="1";[Item Code]="2")))

SALES FY ITEM 2 = CALCULATE(SUM(Tableau1[Sales]); Tableau1[Item Code] = "2")

SALES FY CONV = SWITCH(TRUE();

MIN(Tableau1[Item Code] )= "1"; CALCULATE(SUMX(Tableau1; [SALES FY]); FILTER(Tableau1; OR([Item Code] = "1"; [Item Code] ="2")));

MIN(Tableau1[Item Code]) = "2"; 0;

[SALES FY]

)

Thanks so much for your help!!!

[wdx223_Daniel]

SalesConv :=
VAR vSales_Of_Item2 =
    CALCULATE (
        SUM ( SampleData[Sales] ),
        SampleData[Item] = "Item2",
        ALL ( SampleData )
    )
RETURN
    SUMX (
        VALUES ( SampleData[Item] ),
        VAR vCurrentItem = SampleData[Item]
        RETURN
            IF (
                vCurrentItem = "Item1",
                vSales_Of_Item2 + CALCULATE ( SUM ( SampleData[Sales] ) ),
                IF ( vCurrentItem <> "Item2", CALCULATE ( SUM ( SampleData[Sales] ) ) )
            )
    )

[KARIM-K]

Thank you so much for your help ! Eventhough I didn't fully understand the solution you provided but it worked great 🙂 

Could you please provide me with some more explanations about the solution and specially why do we need to use the All function adn what is the rational behind the function SUMX(VALUES

Thanks again and best regards 

[AlB]

Hi @KARIM-K 

How do you come up withj the 350 in the first row of the expected result?? It does NOT match the logic you provide:

Item 1 quantity consumption = its initial Quantity + 1,8 x Quantity consumtion Item 2

Can you explain?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[KARIM-K]

Hi AIB,

Sorry for the mistake. You are right. For showcase purpose, I didn't apply the 1,8 gross up. I just added up the value of item 2 to item 1 and zero out item 2. It is the same logic but I have just changed the formula for simplicity purpose.

BR