Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Plin0987
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

 

Sales Header:

Invoice NoCustomer ID
468001110
468002111
468003110
468004112

 

Sales Details:

Document NoItem NoQty
468001100-10110
468001200-205790
468001300-40420
468002785-1581
468002648-42758
468002124-4623
468003127-752960
468003129-45220
468004124-4751
468004123-4783

 

Items

Item NoItem CategoryWeight
100-1014315
200-2054325
300-4044320
785-1586814
648-427BC5
124-462411
127-7521025
129-452RA3
124-475435
123-4784318

 

The wanted result is that only these transactions are viewed after the filter is applied:

Invoice NoItem NoQty
468001100-10110
468001200-205790
468001300-40420
1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @Plin0987 ,

 

tomfox_0-1644437498536.png

 

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

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @Plin0987 
Here is the solution exactly as you wish using a measure https://www.dropbox.com/t/lNGNxYZvB6Hao1Sn
Untitled.png
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] )
    )
tackytechtom
Super User
Super User

Hi @Plin0987 ,

 

tomfox_0-1644437498536.png

 

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

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

 @tackytechtom 

 

That did help me. Thanks for the help!

tackytechtom
Super User
Super User

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.tackytech.blog

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! linkedIn

#proudtobeasuperuser 

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 🙂

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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