Is it possible to use SUMX with two tables - different granularity

[MichaelDenmark]

Hello all,
I "think" I need to do some dax in order to calculate total operation time in production, based on total sales from my sales transaction table. 

I have 3 tables (see picture)

The first table include all sales transactions with quantity per item per document invoice. About 10 million rows.
Each of these item sales is produced out of different material components from table 2.

Table 2 include all material components (in time hours) for each item. About 200.000 rows.

Table 3 is a simple Item table, which can have a one-to-many relationship to both sales transactions and Production time.

I would now, for instance, like to show the sum of Setup time and Run time used by each Work Center - which is a field in my production table. I think this calculation needs to be done inside a measure, since creating a fact table (crossjoin sales transaction & Production time) will have too many rows. 

Unfortunately I was not able to create a working DAX formula.

The problem is that I needs to calculate setup (and Run) time for every single sales transactions line (therefore SUMX I assume), but since there is no relationsship between the tables, I have not manage to calculate it correctly.

For instance, the Sum of Run time should be calculated by multiplying Quantity (from sales trans) * Run time (Prod table), and the setup time should be calulacted by multiplying each unique documentNo * Setup Time.

Is this possible, and do anyone have a solution to my case?
I have written this post, and think it is kind of the same I need to do:
https://community.powerbi.com/t5/Get-Help-with-Power-BI/ct-p/PBI_GetHelp

Kind regards

[v-jialluo-msft]

^{Hi @MichaelDenmark ,}

^{Please follow these steps:}

^{(1) Create a new measure}

COUNT = 
VAR _ITEM =
    CALCULATETABLE (
        VALUES ( 'Production time'[Item Name] ),
        'Production time'[Work Center] = MAX ( 'Production time'[Work Center] )
    )
VAR _X =
    CALCULATE (
        DISTINCTCOUNT ( 'Sales Transaction'[Document No] ),
        FILTER ( ALL ( 'Sales Transaction' ), 'Sales Transaction'[Item Name] IN _ITEM )
    )
RETURN
    _X

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

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

[MichaelDenmark]

Hi @v-jialluo-msft Your solution work on my run time, since it is able to calculate the correct amount of quantity for each Work center. However, I cannot get my unique count to work, with the same logic.

I have the following calculation:

Transactions =

VAR _ITEM = CALCULATETABLE(VALUES('D_Item Parent Routing'[Parent Item No_]),'D_Item Parent Routing'[Work Center Global Dimension 1 Code] = MAX('D_Item Parent Routing'[Work Center Global Dimension 1 Code]))

VAR _SALE = SUMX(FILTER('F_Sales Transactions','F_Sales Transactions'[No_] IN _ITEM ) , CALCULATE(DISTINCTCOUNT('F_Sales Transactions'[Document No_])))

RETURN _SALE

But it calculated all rows. Do you have a solution to this problem? 🙂

[MichaelDenmark]

I have attached a wrong link, to a simular solution.

Below is the correct link: 
https://community.powerbi.com/t5/DAX-Commands-and-Tips/SUMX-over-multiple-tables/td-p/2408887

[v-jialluo-msft · Accepted solution]

^{Hi @MichaelDenmark ,}

^{Please follow these steps:}

^{(1) Create a new measure}

QUANTITY =

VAR _ITEM = CALCULATETABLE(VALUES('Production time'[Item Name]),'Production time'[Work Center] = MAX('Production time'[Work Center]))

VAR _SALE = SUMX(FILTER(ALL('Sales Transaction'),'Sales Transaction'[Item Name] IN _ITEM ) , 'Sales Transaction'[Quantity])

RETURN _SALE

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

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

[MichaelDenmark]

Hi Gallen,

Thanks - I will give it a try, and mark as accepted if it fix my issue 🙂