Forum Discussion

benjaminlperry's avatar
benjaminlperry
Frequent Visitor
7 years ago
Solved

SUM removing duplicates

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 t...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
7 years ago

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.
Mariusz Repczynski

 

benjaminlperry's avatar
benjaminlperry
Frequent Visitor
7 years ago

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's avatar
    Mariusz
    Community Champion
    7 years ago

    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.
    Mariusz Repczynski