Sum only positive values from different rows for a period of time

[dshandrov]

Hi Guys,

I need help with the following case:

I have a table with fields for date, item, category and value,

There a three types of items a, b and c, and two types of categories x1 and x2 where 1 means that the value in the value field must remain positive and 2 means that the value must be converted to a negative. 

I need to create a matrix whre I have all the items and calculate the sum of all values (positive and negative) for specific period and show values that after the sum are positive.

table

Date	Item	Category	Value	Converted Value	sum
01-01-23	A A	x2 x1	2 3	-2 3	1
02-01-23	B	x1	3	3	3
03-01-23	A	x2	4	-4	-4
04-01-23	C C	x1 x2	3 1	3 -1	2
05-01-23	C	x1	1	1	1
06-01-23	B B	x2 x1	3 2	-3 2	1
07-01-23	C	x1	2	2	2
08-01-23	C	x2	5	-5	-5
09-01-23	B	x1	1	1	1
10-01-23	A	x1	2	2	2

result table

Item	Result	Result to Show
A	-1
B	5	5
C	-1
total	3	5

Can you please help me with some directions?

Thanks!

[Dangar332 · Accepted solution]

hi, @dshandrov 

create new column for  converted value

Column = 
IF(
    'Table'[Category]="x1" || 'Table'[Value]<0, 
    ABS('Table'[Value]),
    IF(
     'Table'[Category]="x2" || 'Table'[Value]>0
     ,'Table'[Value]*-1
    )
)

look like below

then use below measure for metrix

Measure = 
var a = CALCULATE(min('Table'[Item]),FILTER('Table',SUM('Table'[Column])<0))
var b = CALCULATE(SUM('Table'[Column]),'Table'[Item]<>a)
var c = IF(
          ISINSCOPE('Table'[Item]),
          IF(
             SUM('Table'[Column])<0,
             BLANK(),
             SUM('Table'[Column])
            ),
            b
        )
return
c

your result look like below

you can download file click HERE