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
Icon for Community Champion rankCommunity 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.

  • Plin0987's avatar
    Plin0987
    Frequent Visitor
    4 years ago

    Thanks for your reply tamerj1  

     

    It did show the wanted results in the PBI you attached, but I get an error when applying it to my model. I looked at it for quite a while and then tried to replicate your model with the simplified data but still I ended up getting the same error message in the simplified model. 

     

    The error message I get when trying to add "Total Qty"-measure to the matrix is: 

    "MdxScript(Model) (191, 41) Calculation error in measure 'Sales Details'[Total Weight]: A table of multiple values was supplied where a single value was expected."

     

    I'll post the measures made by tamerj1   in case the link is gone and someone else might be helped/inspired by it.

     

    Total Weight = 
VAR CurrentInvoiceNo = VALUES ( SalesHeader[Invoice No] )
VAR TotalInvoiceWeight =
    CALCULATE (
        SUMX (
            SalesDetail,
            SalesDetail[Qty] * RELATED ( Items[Weight] )
        ),
        SalesDetail[Document No] = CurrentInvoiceNo,
        REMOVEFILTERS ( Items )
    )
VAR Result =
    IF ( 
        TotalInvoiceWeight >= 20000,
        TotalInvoiceWeight
    )
RETURN
    Result

     

    Total Qty = 
    IF (
        NOT ISBLANK ( [Total Weight] ),
        SUM ( SalesDetail[Qty] )
    )