Forum Discussion
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
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 |
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.instagram.com/tackytechtom
6 Replies
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.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 ResultTotal Qty = IF ( NOT ISBLANK ( [Total Weight] ), SUM ( SalesDetail[Qty] ) )
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.instagram.com/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
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 🙂
