Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sum and Summarize from other table

Hi All,

How could I create a column in "ProductMaster" table, with a sum of the "linetotal" column in "OrderDetail_All" table, filtered by the "productID" column. A simple SUM gives me the sum of all the values ando not by ProductID.

 

 

Regards

 

 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.
    Sample data:

    Column = 
CALCULATE (
    SUM ( OrderDetail_All[LindTotal] ),
    FILTER ( OrderDetail_All, [ProductID] = EARLIER ( ProductMaster[ProductID] ) )
)

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

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

3 Replies

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

    Anonymous , It should do the correct sum, Unless

     

    1. Join is inactive 

    2. You are using product ID from another table

    3. You have a measure where you have used all or allselected , or filter without filter function

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    3 years ago

    What are your relationship keys between those two tables ?  Make sure that direction is 1-M and cross filtering is set to single.

     

    Also share the DAX of your "Total sales" Measure.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.
    Sample data:

    Column = 
CALCULATE (
    SUM ( OrderDetail_All[LindTotal] ),
    FILTER ( OrderDetail_All, [ProductID] = EARLIER ( ProductMaster[ProductID] ) )
)

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

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