SUM removing duplicates

[benjaminlperry]

So I have a table with a list of inspections, and then another related table with a list of all violations associated with the inspections.  The inspection table and violation table are related via the report #.  

Violation Table:

Report Number    Violation Code     Violation Points

1                                    1.3                          2

1                                    1.8                          3

1                                    1.3                          2

2                                    12.4                        1

2                                     7.5                         5 

What I am attempting to do on the Inspection Table is to create a calculated column with a SUM of the violation points for that report #.  That's easy enough to do....

Inspection Points = CALCULATE(SUM('Violation Table'[violation points]))

BUT I want to filter out duplicate violation codes on each individual report #, so the inspection table would look like this... Total Inspection Points for Report #1 = 5 not 7.

Report Number          Total Inspection Points

      1                                      5

      2                                      6

This isn't working for me..... 

Inspection Points = CALCULATE(SUM('Violation Table'[violation points]), DISTINCT('Violation Table'[Violation Code])

[Mariusz · Accepted solution]

Hi @benjaminlperry 

You can use the below expression.

Total Inspection Points = 
SUMX( 
    GROUPBY( 
        'Violation Table', 
        'Violation Table'[Report Number], 
        'Violation Table'[Violation Code], 
        'Violation Table'[ Violation Points] 
    ), 
    'Violation Table'[ Violation Points] 
) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[benjaminlperry]

I believe this works, but would it filter out duplicates of the same point value even if they have different violation codes?  I wouldn't want duplicated point values filtered, just duplicate violation codes.

[Mariusz]

Hi @benjaminlperry 

 Group by will create a unique combination of all columns listed within it, so if there is a scenario like below it will count it as 4 

Report Number    Violation Code     Violation Points

1                                    1.3                          2

1                                    1.8                          2

Hope this helps!

Best Regards,
Mariusz

Please feel free to connect with me.