Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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]
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,
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:
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]
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
I think the formula you found is even better, since its processing time will be faster than the method I provided.
User | Count |
---|---|
100 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |