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] )
    )