Sum with multiple criteria

[Plin0987]

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 information needed: Sales Header, Sales Details, Items

Sales Header:

Invoice No	Customer ID
468001	110
468002	111
468003	110
468004	112

Sales Details:

Document No	Item No	Qty
468001	100-101	10
468001	200-205	790
468001	300-404	20
468002	785-158	1
468002	648-427	58
468002	124-462	3
468003	127-752	960
468003	129-452	20
468004	124-475	1
468004	123-478	3

Items

Item No	Item Category	Weight
100-101	43	15
200-205	43	25
300-404	43	20
785-158	68	14
648-427	BC	5
124-462	41	1
127-752	10	25
129-452	RA	3
124-475	43	5
123-478	43	18

The wanted result is that only these transactions are viewed after the filter is applied:

Invoice No	Item No	Qty
468001	100-101	10
468001	200-205	790
468001	300-404	20

[tamerj1]

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]

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

[tackytechtom · Accepted solution]

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]

 @tackytechtom 

That did help me. Thanks for the help!

[tackytechtom]

Hi @Plin0987 ,

How about using the filter pane and adding your filter requirements there?

Or do you need a special measure for this?

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[Plin0987]

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 🙂