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

     

  • Plin0987's avatar
    Plin0987
    Frequent Visitor
    4 years ago

    Hi tackytechtom 

     

    I'm still taking my first baby steps with Power BI but if I understand it correctly, using the filter pane would only allow me to filter on transaction level (sales details) in this case? However, yes I would like to have it as a measure to do further visualisations. The measure would calculate if the order was transported by a full trailer directly from the distributor to customer or if the customer bought smaller volumes from our local site.

     

    I would like to sum it at the Invoice No level. For example, the wanted result for Invoice No 468001 that I posted at the end would sum up to 150 + 19.750 + 400 = 20.300 and all of these items are within the Item Category "43".

     

    I'm sure there are posted solutions somewhere but being new to DAX and being non-English native speaker, I'm yet a bit lost with what keywords to use. My aim is to level up to par with my google skills within excel 🙂