Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Multiply columns from 2 tables by using a measure

Table A: 

PartNoRefNoTypePartQty
A1233532-421X5
B2345754-643Y3
C3424362-346X2
A3526853-435Z6

 

Table B

RefNoPeriodRefQty
3532-421M1100
6853-435M1200
6853-435M250
4362-346M3400

 

I would like to create a measure UseQty.

UseQty = RefQty * PartQty when RefNo (in Table B)= RefNo (in Table A)

Eventually I would like to create charts using Period (Table B), PartNo (Table A), Type(Table A) and UseQty (Measure).

 

I tried creating an additional column in Table B via PowerQuery but that ended up making the calculation very slow as I have 60k rows in Table A and 400k rows in Table B. 

 

Appreciate your help!!!

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi Anonymous,

     

    Do you have repeated values on the refno of table A? Asking this because this changes the result you need.

     

    Create a table with the refn.º and make a relationship between this table and the other 2 with a one to many now add the following measure:

     

    TotalQTY =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            TableA,
            Reference[RefNo],
            TableA[PartNo],
            "RefQty", SUM ( TableA[PartQty] )
        ),
        "Quantity",
            CALCULATE (
                SUM ( TableB[RefQty] ),
                TableB[RefNo] IN VALUES ( Reference[RefNo] )
            )
    ),
    [RefQty] * [Quantity]
)

     

    Result below:

     

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous,

     

    Do you have repeated values on the refno of table A? Asking this because this changes the result you need.

     

    Create a table with the refn.º and make a relationship between this table and the other 2 with a one to many now add the following measure:

     

    TotalQTY =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            TableA,
            Reference[RefNo],
            TableA[PartNo],
            "RefQty", SUM ( TableA[PartQty] )
        ),
        "Quantity",
            CALCULATE (
                SUM ( TableB[RefQty] ),
                TableB[RefNo] IN VALUES ( Reference[RefNo] )
            )
    ),
    [RefQty] * [Quantity]
)

     

    Result below: