Forum Discussion

jja's avatar
jja
Icon for Helper III rankHelper III
4 years ago
Solved

calculate sum between 2 tables

Hi

 

I have a table Components which conist of component name, weight

then i have table Transactions where it stores different transactions of components. I have relationship between tables 1-*

 

I want to sum in a visual table the total weight of transactions the user made. I can sum the Processed qty but fail to sum Processed weight. i see if component has weight of 2 kg and has 3 different transactions and if i just multiply weight and qty each record return me 6 kg and then the overall sum is incorect

The table looks looks somehting like this:

User|Processed qty|Processed weight

 

what i am doing wrong? pls help

 

 

Need Help
  • calerof's avatar
    calerof
    4 years ago

    Hi jja ,

     

    If you use this code you can calculate the total weight:

     

    Total Weight = 
SUMX(
    Component_Transaction,
    [Quantity Total] * RELATED(Component[Weight])
)

     

    With a transaction table like this:

     

    This will be your result:

    Hope it helps.

    Regards,

    Fernando

  • amitchandak's avatar
    amitchandak
    4 years ago

    jja , Hope I got the tables name right

     

    Sumx(Transactions , [Processed qty]*  related(Component[Processed weight]))

5 Replies

    • jja's avatar
      jja
      Icon for Helper III rankHelper III
      4 years ago

      Hi amitchandak 

      Processed weight is a measure that i want to achieve and weight column is stored in Components table
      Transactions table has qty column and Processed qty measure

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

        jja , Hope I got the tables name right

         

        Sumx(Transactions , [Processed qty]*  related(Component[Processed weight]))

  • calerof's avatar
    calerof
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Hi jja ,

     

    If you use this code you can calculate the total weight:

     

    Total Weight = 
SUMX(
    Component_Transaction,
    [Quantity Total] * RELATED(Component[Weight])
)

     

    With a transaction table like this:

     

    This will be your result:

    Hope it helps.

    Regards,

    Fernando