Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SUMX FROM 2 DIFFERENT TABLES (DAX)

Hi Guys,

 

Newbie here !

 

Just wanna ask how to sumx from 2 different tables?

 

Thank you!

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    just sum them 🙂

    sumxtwice = SUMX('Table1';'Table1'[Column1])+SUMX('Table2';'Table2'[Column2])

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • edhans's avatar
    edhans
    6 years ago

    If you need a single SUMX for two fields in different tables, use something like the following:

     

    Measure =
SUMX(
   TableName,
   TableName[Field] * RELATED(TableName2[DifferentField])
   )

    The tables have to have a relationship, and this assumes you are going from the many table to the one table. For example, you are multiplying quantities in a sales fact table against the cost of goods from a product dimension table. 

8 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    If you need a single SUMX for two fields in different tables, use something like the following:

     

    Measure =
SUMX(
   TableName,
   TableName[Field] * RELATED(TableName2[DifferentField])
   )

    The tables have to have a relationship, and this assumes you are going from the many table to the one table. For example, you are multiplying quantities in a sales fact table against the cost of goods from a product dimension table. 

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hello Anonymous 

     

    just sum them 🙂

    sumxtwice = SUMX('Table1';'Table1'[Column1])+SUMX('Table2';'Table2'[Column2])

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • dondanni's avatar
      dondanni
      New Member
      4 years ago

      What if I want to use sumx table and multiply it with another another table on row leavel. (use  first sumx as base table and multiply from another table on row leavel. ) 
      sumxMultiply = SUMX('Table1';'Table1'[Column1]) * SUMX('Table2';'Table2'[Column2])
      I think totla sum will be not be right.  

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        4 years ago

        It won't be. You'd need to do it through a common dimension table

         

        test measure =
SUMX(
    Products,
    RELATED( table1[field] )
        * RELATED( table2[field] )
)

         

        But that won't be right unless the product table is at the right granularity. You should probably do a merge in Power Query and do the math there.

        But either way, this should be a new thread. This post was marked solved over a year ago.

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hello Anonymous 

    have you been able to solve the problem with the replies given?

    If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    All the best

    Jimmy

  • MuradMusleh's avatar
    MuradMusleh
    Frequent Visitor
    4 years ago
    Total Revenue =
    SUMX( Sales,
                    Sales[Quantity_Sold] * ( RELATED ( Products[Unit_Price] ) - RELATED ( Products[Unit_Cost]  )   )
    )