cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Proud to be a Super User!

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

Proud to be a Super User!

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

Proud to be a Super User!

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors