SUMX with FILTER calculation in the same table

[Anonymous]

I have a table as follows that I need to sum up all the quantities and apply the sum to the product:

Sales
PN	Qty	Total Qty
AB	2	5
BA	4	8
BA	4	8
CA	5	5
AB	2	5
AC	2	4
AC	2	4
AB	1	5

When I try to use the following formula: Total QTY = SUMX(FILTER(Sales, Sales(PN) = Sales(PN)),Sales(Qty)) 

This is what I get where it gives me the total sum of the Qty column:

Sales
PN	Qty	Total Qty
AB	2	22
BA	4	22
BA	4	22
CA	5	22
AB	2	22
AC	2	22
AC	2	22
AB	1	22

It looks like it is not recognizing the expression "Sales(PN) = Sales(PN)". Is there a way to make the formula work?

[mahoneypat · Accepted solution]

Sales(PN) = Sales(PN) is always true, so all your rows are included in the total.  Please try this expression instead.

NewMeasure = CALCULATE(SUM(Sales[Qty]), ALLEXCEPT(Sales, Sales[PN]))

To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

[Anonymous]

Thank you Pat! This worked perfectly.

[ryan_mayu]

@Anonymous 

please try

Measure = CALCULATE(sum('Table'[Qty]),ALL('Table'))