Forum Discussion
Sum a column if another column has repeating values
Hello,
I have a table that follows this structure:
| Code | Level | Total |
| 111 | A | 1 |
| 111 | A | 1 |
| 222 | B | 1 |
| 333 | B | 2 |
| 333 | B | 1 |
| 111 | C | 1 |
| 222 | D | 1 |
| 444 | D | 2 |
As you can see, the same "Level" can have more than one distinct "Code".
I want to create a measure that sums the "Total" column only if the "Level" is repeated for more than one distinct "Code".
So I wanted a way to know the sum of the "Total" column for each "Level" that is repeated in distinct Codes.
Based on the table above, this measure would bring me the following sum for each Level:
A = 0
B = 4
C = 0
D = 3
Try this:
MeasureTotal =
VAR __table = SUMMARIZE(Table2,Table2[Code],Table2[Level],"count",DISTINCTCOUNT(Table2[Code]),"sum",sum(Table2[Total]))
VAR _table2 = SUMMARIZE(__table,Table2[Level],"countCode",sumx(__table,[count]),"sumtotal",sumx(__table,[sum]))
RETURN IF(sumx(_table2,[countCode])>1,sumx(_table2,[sumtotal]),0)
1 Reply
andhiii079845Solution Sage
Try this:
MeasureTotal =
VAR __table = SUMMARIZE(Table2,Table2[Code],Table2[Level],"count",DISTINCTCOUNT(Table2[Code]),"sum",sum(Table2[Total]))
VAR _table2 = SUMMARIZE(__table,Table2[Level],"countCode",sumx(__table,[count]),"sumtotal",sumx(__table,[sum]))
RETURN IF(sumx(_table2,[countCode])>1,sumx(_table2,[sumtotal]),0)