Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
s-in-a-triangle
Frequent Visitor

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

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?

 

1 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:

7.JPG8.JPG

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
s-in-a-triangle
Frequent Visitor

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.

 

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:

7.JPG8.JPG

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lili6-msft
Community Support
Community Support

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:

2.JPG

Result:

3.JPG

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:
4.JPG
 

Best Regards,

Lin

 
Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PattemManohar
Community Champion
Community Champion

@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)





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.