cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

 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
1 ACCEPTED SOLUTION
Super User

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

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

6 REPLIES 6
Super User

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.

Frequent Visitor

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] )
)``````
Super User

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

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Frequent Visitor

That did help me. Thanks for the help!

Super User

Hi @Plin0987 ,

Or do you need a special measure for this?

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Frequent Visitor

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 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors