Get sum in table visual where some values are only count once

[Crydus]

Hey there, 

I am reaching the end of my (still very limited) Power BI knowledge. I am currently creating a quality measure report where with a table visual which shows the amount of defect pieces per delivery and the table should also show the total amount of defect pieces. 
We have a distinct number for each complaint, which can have multiple items and kinds of defects. The problem here is that there are some cases where a complaint has the same item with different kinds of defects. In this cases I want to count these values only once, but I still want to be able to show them all or to filter them by their defect category. (I hope you get my point here) 

Unfortunately I can't provide any direct data from the report as it is confidential. But I have tried to create an Excel table with shows the issue. 

ComplaintNo	Item	FailureCategory	Defect Parts
C0001	I0001	F01	10
C0002	I0001	F01	5
C0002	I0001	F02	5
C0002	I0001	F03	5
C0002	I0003	F02	5
C0003	I0004	F02	10
C0003	I0005	F01	10
C0003	I0005	F02	10
C0004	I0006	F01	5
C0005	I0007	F02	5
C0006	I0008	F02	10
C0007	I0009	F01	5

Currently the visual would show a sum of 85 defect parts, although in this case only 65 should be counted. 

I have tried to create a calculated column where only the first occurence in the data table is shown and all other values where ComplaintNo and Item are the same are displayed with Zero, in this case the sum would be right if no filters are applied on the visual, but as soon as I would filter the table to see only F02 complaints the sum would be wrong again. 

I hope that you can understand what I mean and are able to help me 🙂 

Thanks in advance!

[nandukrishnavs · Accepted solution]

Hi @Crydus,

Please try below the DAX measure.

SumOfDefectParts =
VAR summarizedTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[ComplaintNo],
            'Table'[Item],
            "DefectPartsValue", MIN ( 'Table'[Defect Parts] )
        )
    )
VAR result =
    SUMX ( summarizedTable, [DefectPartsValue] )
RETURN
    result

Let me know if you are finding any issues. 

Regards,

Nandu Krishna

[Crydus]

This seems to be perfect. 
Now I have to only find out, why there is a difference of 2 between this measure and my old calculations but I'm pretty sure that this is due to my calculations. 

Thank you!