SUM with Filter based on a calculation

[Anonymous]

Hi,

I have a Table with 3 colums: ID, a Number (Nr) and Sales:

ID|Nr |Sales

 1|100|$1000

 2|101|$1001

 3|102|$1000

 4|103|$1000

 5|100|$1000

 6|99 |$1000

 7|101|$1002

I would like to have a new column based on the "Nr" column:

 > For each Nr add +1 and make the sum of all rows you find with the new Nr.

For example Row 1 should have the sum of row 2 plus row 7 = $2003

Why: Row 1 has the Nr 100 > 100 plus 1 = 101 > there are two rows with Nr 101 > the sum of Sales for those rows is $ 2003.

I thought this would work - but it doesn't:

CALCULATE(SUMX(DataT;DataT[Sales]);FILTER(ALL(DataT);DataT[Nr]=(DataT[Nr]+1))

Any help?
Thanks!
Chris

[Shawn_Fitz]

I tried with the data you gave, and with what I understand from what you want,

you could try this:

CALCULATE(SUM('Table'[SALES]),FILTER(ALL('Table'), 'Table'[NR] = MAX('Table'[NR]) +1))

This is what it gives me.

[Anonymous]

Hi,

thanks for your answer! Your result looks like the result I need!

However I do not get the same result using your formula - in fact the column is empty:

[Shawn_Fitz · Accepted solution]

Hi @Anonymous ,

Depending on what you need,

The formula I gave, I'm using it in a measure, but if you use it in a calculated column it won't work.

If you really need a calculated column, the first create a measure using the formula I gave.

Then create a calculated column where the column is equal to the measure.

MeaSalesbyNR = CALCULATE(SUM('Table'[SALES]),FILTER(ALL('Table'), 'Table'[NR] = MAX('Table'[NR]) +1))

colSalesbyNR = [MeaSalesbyNR]

[Anonymous]

Thanks so much - sorry not to clarify that I needed a column!

What I also came up with is this solution for a column:

SalesByNr = 
var CurrentNr = Tabelle1[nr]
return
CALCULATE(SUM(Tabelle1[sales]);FILTER(ALL('Tabelle1'); Tabelle1[nr] = CurrentNr +1))

This gives me the same result. 

In your opinion: are there any drawbacks to this solution?

Thanks

Chris

[Shawn_Fitz]

I think the formula you found is even better, since its processing time will be faster than the method I provided.