Forum Discussion
SUM with Filter based on a calculation
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
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]
5 Replies
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.
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]