How to fix the wrong total in table visualization in power BI?

Question

I have a measure name Y_Test, where Y_Test is:

Y_Test = 
VAR Y = SUM(Sheet1[C2])
var result = IF(MAX(Sheet1[C1]) = "A", Y, IF(((MAX(Sheet1[C1])) = "B" || (MAX(Sheet1[C1]) = "C")),  -1 * Y))
Return result

I'm getting the total as -16, shouldn't it be 8?

Thanks in advance.

Answer 1

As an alternative option, you can try an iterator, in this case, SUMX.

The operation would be run for each instance of 'Table'[C1], so you need to be careful if the number of rows for the unique values of C1 is too big.

Measure

Y_TEST =
SUMX (
    VALUES ( 'Table'[C1] ),
    IF ( [C1] = "A", 1, IF ( [C1] IN { "B", "C" }, -1 ) )
        * CALCULATE ( SUM ( 'Table'[C2] ) )
)

Table

C1	C2	C3
A	1	a
B	2	b
C	3	b
D	2	d
A	1	a
B	3	b
C	4	C
D	2	d
A	2	d