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

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

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity 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
        Icon for Community Champion rankCommunity 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