Trying to use SUMX to calculate from two different tables

[nschmidt]

Example tables:

TABLE: Cost Elements

     

TABLE: 2019 Sale Data

Essentially I want to measure the annualized impact of the most recent cost change. I've calculated the most recent cost change by adding a column using rankx to rank the date (1 being the most recent) as the monthly cost file is added every month this will capture the latest:

Cost 1 Change = CALCULATE(SUM('Cost Elements'[Cost 1]),'Cost Elements'[Date Rank]=1)-CALCULATE(sum('Cost Elements'[Cost 1]),'Cost Elements'[Date Rank]=2)

How do I take "Cost 1 Change:" and multply the "2019 Qty Sold" in table 2019 Sales Data table so that it measures the impact across all Item IDs like it does in excel below:

[nschmidt]

This worked great... i just did a sumx then of the cost change * qty sold and it worked great. Pulling the qty sold in over as a variable and the allexcept function is where I was falling short. Thanks!!!

[v-yuta-msft · Accepted solution]

@nschmidt ,

Create calculate columns using dax as below:

Cost 1 Change = 
IF (
    'Cost Elements'[Date Rank] = 1,
    CALCULATE (
        SUM ( 'Cost Elements'[Cost 1] ),
        FILTER (
            ALLEXCEPT ( 'Cost Elements', 'Cost Elements'[Item ID] ),
            'Cost Elements'[Date Rank] = 1
        )
    )
        - CALCULATE (
            SUM ( 'Cost Elements'[Cost 1] ),
            FILTER (
                ALLEXCEPT ( 'Cost Elements', 'Cost Elements'[Item ID] ),
                'Cost Elements'[Date Rank] = 2
            )
        )
)

Table 2019 Sales Data = 
IF (
    'Cost Elements'[Date Rank] = 1,
    VAR Item_ID = 'Cost Elements'[Item ID]
    RETURN
        CALCULATE (
            MAX ( '2019 Sale Data'[2019 Qty Sold] ),
            FILTER ( '2019 Sale Data', '2019 Sale Data'[Item ID] = Item_ID )
        )
)

Cost 1 Change * 2019 Qty Sold = 'Cost Elements'[Cost 1 Change] * 'Cost Elements'[Table 2019 Sales Data]

 

Community Support Team _ Jimmy Tao

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

[amitchandak]

You should group this data bu common time dimension  and should be able to do this

sumx(summarize(Item[Item ID],"_Cost",[Cost 1 Change], "_sum",sum('2019 Sales Data'[2019 Qty Sold])),[_Cost]*[_sum])