Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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 |
Solved! Go to 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.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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
Result
Total 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
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |