How do I only sum up certain values on a matrix (sum measure might be needed)

[KA95]

Hi there,

I have a matrix visual that's built off two datasets, Lineitems and Deals, on this test dataset there are 3 deal names (schools) and each School has several lineitems that are either lineitem type assembly or workshop, and a year group of 7, 8, 9 with a quantity value. As shown below:

I don't want this visual to sum up duplicates, so for London School I want the total to be 1400 for both Assembly and Workshop. If a Deal has the exact same figures on Assembly and the exact same figures on Workshop, I only want to add up either one or the other, if that makes sense. 

So London and Manchester Schools have duplicate values, I only want one LineItem Type summed up or the other. Whereas Newcastle School I want summing up entirely because all values on the LineItem types are different. 

The value that I'm using to sum this data up is [quantity], would this need to be a measure? Here are the sample datasets below so people can understand what the data is.

LineItem Table

LineItem ID	Deal ID	Lineitem Type	Year Group	Quantity
1001	101	Assembly	7	500
1002	101	Assembly	8	500
1003	101	Assembly	9	400
1004	101	Workshop	7	500
1005	101	Workshop	8	500
1006	101	Workshop	9	400
1007	102	Assembly	7	320
1008	102	Assembly	8	320
1009	102	Assembly	9	320
1010	102	Workshop	7	320
1011	102	Workshop	8	320
1012	102	Workshop	9	320
1013	103	Assembly	7	160
1014	103	Assembly	8	340
1015	103	Assembly	9	350
1016	103	Workshop	7	200
1017	103	Workshop	8	245
1018	103	Workshop	9	400

Deal Table

Deal ID	Deal Name
101	London School
102	Manchester School
103	Newcastle School

[danextian]

Hi @KA95 ,

If I get it right, you want to ignore Lineitem Type from your calculation and just based it on all the other columns.  This formula, hopefully, should do the trick. Otherwise please elaborate.

Unique Sum = 
SUMX (
    SUMMARIZE ( LineItem, LineItem[Year Group], LineItem[LineItem ID], LineItem[Quantity] ),
    LineItem[Quantity]
)

[KA95]

No I don't want to ignore it. 

Basically I want it to detect if there are duplicates. So for example, for London School, Assembly and Workshop have the exact same values for year 7, 8, and 9. I want it to only sum up one lineitem type, so the total sum for London School is 1400, instead of 2800. This is exactly the same for Manchester School.

However, for Newcastle School, I want it summed up the way it is as the values from Assembly and Workshop are different and not the same.

[danextian · Accepted solution]

Hi @KA95,

My bad. Lineitem ID shouldn't have been included in the calcuation. The updated formula is

Unique Sum = 
SUMX (
    SUMMARIZE ( LineItem, LineItem[Year Group], LineItem[Quantity] ),
    LineItem[Quantity]
)

Here's the sample output:

This should have worked with just the given data. Basically, it creates a temporary table of Year Group and LineItem Quantity thus ignoring Lineitem Type. The logic is very similar to selecting columns in Excel as criteria for removing duplicates.  Alternatively you can do this in Power Query or by creating a calculated column in DAX to include just one of those with duplicates. Here's a sample calculated column formula in DAX.

With Duplicate = 
VAR _OtherLineitemType =
    CALCULATE (
        MAX ( LineItem[Quantity] ),
        FILTER (
            LineItem,
            LineItem[Year Group] = EARLIER ( LineItem[Year Group] )
                && LineItem[Deal ID] = EARLIER ( LineItem[Deal ID] )
                && LineItem[Lineitem Type]
                    < EARLIER ( LineItem[Lineitem Type] ) //what is less than Workshop in alphabetical order is Assembly, works if there are only two line item types 
        )
    )
RETURN
    LineItem[Quantity] = _OtherLineitemType
   

Please refer to this PBIX for your reference: https://drive.google.com/file/d/1Rnlav71Z0idt6lVRmyI70kjI6JgBLrqe/view?usp=sharing 

[KA95]

Thank you very much @danextian that worked perfectly.