Forum Discussion

Plin0987's avatar
Plin0987
Frequent Visitor
4 years ago
Solved

Sum with multiple criteria

I would like to filter so that I only have transactions with the criteria: - The total weight of the invoice is > 20.000, only counting Items with Item Category = 43   Three tables include the inf...
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi Plin0987 ,

     

     

    I got there in two steps.
    First, I created a calculated column called TotalWeight, where the total weight per Invoice Number is created:

    TotalWeight = 
CALCULATE ( 
    SUMX ( 'Table12_SalesDetails', 'Table12_SalesDetails'[Qty] * RELATED ( 'Table12_Items'[Weight] ) ), 
    ALLEXCEPT ( Table12_SalesDetails,Table12_SalesDetails[Document No] ) 
)

     

    From there, it was pretty easy to create a measure that displays the QTY with your constraints:

    TomsMeasure12 = 
CALCULATE ( 
    SUM (Table12_SalesDetails[Qty]), 
    Table12_Items[Item Category] = "43", 
    Table12_SalesDetails[TotalWeight] > 20000
)

     

    Does this work for you now? 🙂

     

    /Tom

    https://www.tackytech.blog

    https://www.instagram.com/tackytechtom

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Plin0987 
Here is the solution exactly as you wish using a measure https://www.dropbox.com/t/lNGNxYZvB6Hao1Sn

Please let me know if you have any further requirements.
If my reply fulfills your requirement, kindly mark itas accepted solution. Kudos are allways appreciated.