Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
14 | |
12 | |
9 |