cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Remove duplicates from a SUMX function using columns from multiple tables in DAX

Hello All,

I am trying to create a measure were I get the sum of all the prices in one table and multiple it by the quantity of another table. I cannot combine the tables because the data set is too large.

I am currently using:

Measure = SumX(Table1, Table1[Quantity] * Related(Table2[Price]))

This gives me a value, but it is currently summing the duplicate values in the price table. I cannot remove the duplicate values in the price table via power query because I list them out in a different section.

How can I remove the duplicates from the SUMX function?

EDIT: Replaced Sample Data Image to correct version

Sample Data:

Thanks

1 ACCEPTED SOLUTION
Community Support

Hi @msciarrino ,

Please try to create measure with below dax formula:

``````Measure =
VAR cur_id =
SELECTEDVALUE ( Table1[ID Numbers] )
VAR _a =
CALCULATE (
MAX ( Table1[Quantity] ),
FILTER ( ALL ( Table1 ), [ID Numbers] = cur_id )
)
VAR _b =
CALCULATE (
MAX ( 'Table 2'[Price] ),
FILTER ( ALL ( 'Table 2' ), [ID Numbers] = cur_id )
)
VAR _r1 = _a * _b
VAR tmp =
SUMMARIZE (
ALL ( Table1 ),
Table1[ID Numbers],
"QTY", MAX ( Table1[Quantity] )
)
VAR _c =
CALCULATE ( MAX ( Table1[Quantity] ), FILTER ( tmp, [ID Numbers] = cur_id ) )
RETURN
_r1
``````
``Measure 2 = SUMX(VALUES(Table1[ID Numbers]),[Measure])``

Add a table visual with fields and measure:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 REPLIES 9
Community Support

Hi @msciarrino ,

Please try to create measure with below dax formula:

``````Measure =
VAR cur_id =
SELECTEDVALUE ( Table1[ID Numbers] )
VAR _a =
CALCULATE (
MAX ( Table1[Quantity] ),
FILTER ( ALL ( Table1 ), [ID Numbers] = cur_id )
)
VAR _b =
CALCULATE (
MAX ( 'Table 2'[Price] ),
FILTER ( ALL ( 'Table 2' ), [ID Numbers] = cur_id )
)
VAR _r1 = _a * _b
VAR tmp =
SUMMARIZE (
ALL ( Table1 ),
Table1[ID Numbers],
"QTY", MAX ( Table1[Quantity] )
)
VAR _c =
CALCULATE ( MAX ( Table1[Quantity] ), FILTER ( tmp, [ID Numbers] = cur_id ) )
RETURN
_r1
``````
``Measure 2 = SUMX(VALUES(Table1[ID Numbers]),[Measure])``

Add a table visual with fields and measure:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper IV

.

Helper I

This will work, but I want to avoid creating another table due to the large amount of data I am using. I would like to do it as a measure (or calculated column) if possible.

Helper IV

You aren't creating another table. You are merging queries into one.

Helper IV

What is the result you want?

Helper I

Here is a more realistic version of my data and what I am seeing.

Original Data Tables:

Table Visulation with my original sumx formual:

Price*Quantity = SumX(Table1, Table1[Quantity] * Related(Table2[Price]))

My original formula is multiplying the quantites by the price, but since some of the rows are duplicated in Table 1, they are counting it twice.

Table visualization using the formula you provided:

Price*Quantity = DISTINCTCOUNT('Table 2'[ID Number]) * SUM('Table 1'[Price(\$)])

I belive that you formula is taking the Total of the plan Price column and multiplying by the number of IDs, in this case 10 making the sum just 10x of the total price column.

Table visulation I want:

Helper IV

Okay, lets try this. I think this will work better.

1. Did you merge your tables? and remove duplicates?

2. Create first measure =

Measure 1 = SUMX('Table 1','Table 1'[Quantity] * 'Table 1'[Table 2.Price(\$)])
3. Create second measure =
Measure 2 = SUMX(VALUES('Table 1'[Table 2.Price(\$)]),[Measure1])
4. Add fields to a table.
5. My results :

Helper IV

.

Helper I

That did not seem to work for me. When I use that, I get what looks like 10X the value of just adding the price without worrying about the quanties. And there are 10 items in the filtered table I am looking at. I believe that this is counting the items showing then multiplying it by sum of everything quantity 1.

I apoligize. I screwed up my sample data. I reversed the quantity and price. The data should look like this.