topic Re: Sum with multiple criteria in DAX Commands and Tips

Sum with multiple criteria

Plin0987 — Tue, 08 Feb 2022 10:12:13 GMT

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

Re: Sum with multiple criteria

tackytechtom — Tue, 08 Feb 2022 21:00:12 GMT

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

Re: Sum with multiple criteria

Plin0987 — Wed, 09 Feb 2022 07:49:53 GMT

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 🙂

Re: Sum with multiple criteria

tackytechtom — Wed, 09 Feb 2022 20:16:35 GMT

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

Re: Sum with multiple criteria

tamerj1 — Thu, 10 Feb 2022 06:27:49 GMT

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.

Re: Sum with multiple criteria

Plin0987 — Mon, 14 Feb 2022 19:32:46 GMT

tackytechtom

That did help me. Thanks for the help!

Re: Sum with multiple criteria

Plin0987 — Mon, 14 Feb 2022 21:37:56 GMT

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