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

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

Reply
msciarrino
Helper I
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:

msciarrino_0-1700248339984.png

 

Thanks

 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
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:

vbinbinyumsft_0-1700536392038.png

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.

View solution in original post

9 REPLIES 9
v-binbinyu-msft
Community Support
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:

vbinbinyumsft_0-1700536392038.png

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.

Power5
Helper IV
Helper IV

.

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.

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

Power5
Helper IV
Helper IV

What is the result you want?

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

Original Data Tables:

msciarrino_0-1700256099721.png

Table Visulation with my original sumx formual:

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

msciarrino_1-1700256132545.png

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($)])

msciarrino_2-1700256279485.png

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:

msciarrino_3-1700256364082.png

 

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 : 
Power5_0-1700257308495.png

 

Power5
Helper IV
Helper IV

.

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.

msciarrino_0-1700248252832.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.