topic Re: Incorrect result - SUMX two tables in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Incorrect-result-SUMX-two-tables/m-p/2626283#M76661
<P>Hi <LI-USER uid="337817"></LI-USER> </P>
<P>Based on what you've described, here is what I would suggest if I were creating these calculations:</P>
<P> </P>
<P>Create these measures:</P>
<LI-CODE lang="markup">Order Quantity =
SUM ( Orders[Quantity] )</LI-CODE><LI-CODE lang="markup">Total Weight =
SUMX (
SUMMARIZE ( Orders, 'Dimensions'[weight] ),
'Dimensions'[weight] * [Order Quantity]
)</LI-CODE>
<P>The Total Weight measure takes the distinct weights for SKUs appearing in the Orders table, and multiplies these by the corresponding Order Quantity and sums.</P>
<P> </P>
<P>Then for the average weight, did you want the average weight per order?</P>
<P>You would first need a measure that returns the Order Count, either by counting distinct Order Numbers, or counting rows of Orders (whichever is correct), i.e.:</P>
<LI-CODE lang="markup">Order Count =
DISTINCTCOUNT ( Orders[Order Number] )</LI-CODE>
<P>or</P>
<LI-CODE lang="markup">Order Count =
COUNTROWS ( Orders )</LI-CODE>
<P>Then the average weight per order would be:</P>
<LI-CODE lang="markup">Order Average Weight =
DIVIDE ( [Total Weight], [Order Count] )</LI-CODE>
<P> </P>
<P>Does the above help get the result you were looking for?</P>
<P>Please post back if needed.</P>
<P> </P>
<P>Regards,</P>
<P>Owen</P>
<P> </P>Thu, 07 Jul 2022 22:03:46 GMTOwenAuger2022-07-07T22:03:46ZIncorrect result - SUMX two tables
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Incorrect-result-SUMX-two-tables/m-p/2626252#M76659
<P>Hi all, </P><P>I am working with SUMX for the first time and with two different tables.</P><P> </P><P>Yes, I have look at other similar posts, and my formula isn't throwing errors, but it isn't getting the correct result. </P><P> </P><P>I have two tables - Dimensions has a SKU number, height, weight, length and width. Each SKU is unique</P><P>Orders has Order Number, SKU number, quantity, and then several other columns not needed for this calculation. </P><P> </P><P>I am trying to figure out the average weight of orders per day. </P><P> </P><P>Here is my formula - </P><DIV><DIV><SPAN>New Measure Try = </SPAN><SPAN>sumx</SPAN><SPAN>(Orders,</SPAN><SPAN> </SPAN><SPAN>Orders[Quantity]</SPAN><SPAN> * </SPAN><SPAN>AVERAGE</SPAN><SPAN>(</SPAN><SPAN>'Dimensions'[weight]</SPAN><SPAN>))</SPAN></DIV><DIV> </DIV><DIV><SPAN>This is getting larger numbers than when I do it in Excel. Is the "Average" the issue? I can't seem to tell it to do SUMX without using some kind of calculation for the second table. The two tables have a one-many relationship, with Dimensions being the one. </SPAN></DIV><DIV> </DIV><DIV><SPAN>Any help is super appreciated!</SPAN></DIV></DIV>Thu, 07 Jul 2022 21:19:29 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Incorrect-result-SUMX-two-tables/m-p/2626252#M76659Thigs2022-07-07T21:19:29ZRe: Incorrect result - SUMX two tables
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Incorrect-result-SUMX-two-tables/m-p/2626283#M76661
<P>Hi <LI-USER uid="337817"></LI-USER> </P>
<P>Based on what you've described, here is what I would suggest if I were creating these calculations:</P>
<P> </P>
<P>Create these measures:</P>
<LI-CODE lang="markup">Order Quantity =
SUM ( Orders[Quantity] )</LI-CODE><LI-CODE lang="markup">Total Weight =
SUMX (
SUMMARIZE ( Orders, 'Dimensions'[weight] ),
'Dimensions'[weight] * [Order Quantity]
)</LI-CODE>
<P>The Total Weight measure takes the distinct weights for SKUs appearing in the Orders table, and multiplies these by the corresponding Order Quantity and sums.</P>
<P> </P>
<P>Then for the average weight, did you want the average weight per order?</P>
<P>You would first need a measure that returns the Order Count, either by counting distinct Order Numbers, or counting rows of Orders (whichever is correct), i.e.:</P>
<LI-CODE lang="markup">Order Count =
DISTINCTCOUNT ( Orders[Order Number] )</LI-CODE>
<P>or</P>
<LI-CODE lang="markup">Order Count =
COUNTROWS ( Orders )</LI-CODE>
<P>Then the average weight per order would be:</P>
<LI-CODE lang="markup">Order Average Weight =
DIVIDE ( [Total Weight], [Order Count] )</LI-CODE>
<P> </P>
<P>Does the above help get the result you were looking for?</P>
<P>Please post back if needed.</P>
<P> </P>
<P>Regards,</P>
<P>Owen</P>
<P> </P>Thu, 07 Jul 2022 22:03:46 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Incorrect-result-SUMX-two-tables/m-p/2626283#M76661OwenAuger2022-07-07T22:03:46ZRe: Incorrect result - SUMX two tables
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Incorrect-result-SUMX-two-tables/m-p/2627674#M76734
<P>Ah! Perfect! Thanks SO much!</P><P> </P>Fri, 08 Jul 2022 12:52:32 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Incorrect-result-SUMX-two-tables/m-p/2627674#M76734Thigs2022-07-08T12:52:32Z