Sum over a colum values only when the running total of the column value is positive

[s-in-a-triangle]

Hi all,

My data looks like this:

category1	category2	code	date	amount
1	a	123	1-1-2018	5
2	a	123	1-3-2018	-10
3	a	123	1-5-2018	15
4	a	456	1-1-2018	-10
1	a	456	1-3-2018	20
2	a	789	1-5-2018	15
3	b	444	1-1-2018	10
4	b	444	1-3-2018	25

What I'm looking for is summing the total amount ONLY over the codes for which the running total of code amount is greater than 0/not negative.

For instance, the correct numbers I'm looking for are:

- Category 2 value A in 1-1-2018 is 5 (since the running total of code 123 is 5 and the negative running total of -10 of code 456 I do not want to count).

- Category 2 value A in 1-3-2018 is 10 (since the running total of code 456 is 10 and the negative running total of -5 of code 123 I do not want to count).

- Total in 1-3-2018 is 45 (since the running total of code 456 is 10, the running total of code 444 is 35, and the negative running total of -5 of code 123 I do not want to count).

- Total overall is 70

I can get the running total by code values with the following column

sum_amount_column = CALCULATE(SUM(Blad1[amount]);FILTER(ALLEXCEPT(Blad1;Blad1[code]);Blad1[date]<=EARLIER(Blad1[date])))

   or the following measure

sum_amount_measure = CALCULATE(SUM(Blad1[amount]);FILTER(ALLEXCEPT(Blad1;Blad1[code]);Blad1[date]<=max(Blad1[date])))

How to sum only over the codes that have a positive running total?

[s-in-a-triangle]

Hi,

Thank you for your quick reply.

Unfortunately my problem is not solved yet. What I'm looking for is aggregating up to a higher dimension. I want to have the  total amount for category 2 in a given time period, YET only sum the amounts for which the running total amount of the underlying code is not negative.

Desired outcome:

When slicing the date to 02-01-2018:

category2	sum of amount
a	5
b	10

Codes with running totals that are negative are not included in the summation. Thus, code 456 will not be included.

When slicing the date to 01-01-2018 - 03-03-2018:

category2	sum of amount
a	10
b	35

Codes with running totals that are negative are not included in the summation. Thus, code 123 will not be included.

(Please forget about category 1)

I'm playing around with a measure like

Measure = SUMX(FILTER(Blad1;[sum_amount_measure]>0);Blad1[amount]) 

but cannot get it fully correct yet.

[v-lili6-msft · Accepted solution]

hi, @s-in-a-triangle

After my research, you could try these formula to create the measure:

Step1:

Adjust your sum_amount_measure formula

sum_amount_measure = CALCULATE(SUM(Blad1[amount]),FILTER(ALLEXCEPT(Blad1,Blad1[category2],Blad1[code]),Blad1[date]<=max(Blad1[date])))

Step2:

use this formula to create a result measure

Measure 3 = var _table=SUMMARIZE(Blad1,Blad1[category2],Blad1[code],"a",[sum_amount_measure]) return
CALCULATE(SUMX(FILTER(_table,[a]>0),[a]))

Result:

Best Regards,
Lin

[v-lili6-msft]

hi, @s-in-a-triangle

You may try to set visual level filter to filter sum_amount_column is greater than 0 or sum_amount_measure is greater than 0 

like below:

Result:

for total of sum_amount_measure is 70, it is a measure total problem, you could refer to this post:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

create a new measure like 

Measure = SUMX(Blad1,[sum_amount_measure])

Result:

Best Regards,

Lin

[PattemManohar]

@s-in-a-triangle Could you please post an additioanl expectedoutput column with the result that you are expecting for each row. That makes better understanding (For me your statements are not clear)