Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Proud to be a Super User!
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]
)
Proud to be a Super User!
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.
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |