Forum Discussion
Un-accumulate a set values
Thanks for replying so quickly.
Apologies, I have multiple sets of periods. e.g. 2021 Actual, 2021 Budget, 2022 Actual, 2022 Budget etc. Even though on the current page I have a Filter so there is only 1 of each period.
Also, sorry I should have said before, the screenshots are not the raw data, they're already 'grouped'. The raw data is more like the below, so multiple entries for 1, 2 etc.
I did as you suggested above and only P1 is correct, I am assuming that's something to do with this?
Raw data structure example (simplified):
| File | Period | Department | Type | Amount |
| Budget | 1 | A | Revenue | 1 |
| Budget | 1 | B | Cost | 2 |
| Budget | 2 | A | Revenue | 3 |
| Budget | 2 | B | Cost | 4 |
| Budget | 3 | A | Revenue | 6 |
| Budget | 3 | B | Cost | 7 |
| Budget | 4 | A | Revenue | 10 |
| Budget | 4 | B | Cost | 11 |
| Actual | 1 | A | Revenue | 1 |
| Actual | 1 | B | Cost | 2 |
| Actual | 2 | A | Revenue | 3 |
| Actual | 2 | B | Cost | 4 |
| Actual | 3 | A | Revenue | 6 |
| Actual | 3 | B | Cost | 7 |
| Actual | 4 | A | Revenue | 10 |
| Actual | 4 | B | Cost | 11 |
Desired result (example of 1 sub group):
| File | Period | Department | Type | Amount | Amount per Period |
| Budget | 1 | B | Cost | 2 | 2 |
| Budget | 2 | B | Cost | 4 | 2 |
| Budget | 3 | B | Cost | 7 | 3 |
| Budget | 4 | B | Cost | 11 | 4 |
So Amount per Period is the value I am trying to create.
Sure, this works for the data you posted, is the real data much more complicated?
Amount per Period =
var _period = 'Table'[Period]
var _previous =
CALCULATE(
MAX('Table'[Amount]),
'Table'[Period] < _period,
ALL('Table'[Amount])
)
return 'Table'[Amount] - _previous
Thanks. I can also get this to work for my test data:
But unfortunately it isn't working for my actual dataset. When adding the new column and Period to a table, it still shows the accumulated values i.e. the same as the SUM of (my equivalent of) Test[Amount]? There are many more columns and rows of data in the real data as opposed to my example, but unfortunately I can't share it.
My real data contains negative values, but I just did the above test data with negative values and it was OK so I don't think it can be that.
Ok, so for more columns can you give this a try?
Amount per Period = var _period = 'Table'[Period] var _previous = CALCULATE( MAX('Table'[Amount]), 'Table'[Period] < _period, ALLEXCEPT('Table','Table'[File],'Table'[Department],'Table'[Type]) ) return 'Table'[Amount] - _previousThe columns inside the ALLEXCEPT need to be the ones you're looking to group by.