Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |