Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey there,
I want to remove rolling total/cumulative effect from a sequence of amounts?
My data looks like this
Date | Account | Region | Amount |
31/01/2016 | n123456 | US | 10 |
31/03/2016 | n123456 | US | 25 |
31/08/2016 | n123456 | US | 70 |
30/09/2016 | n123456 | US | 71 |
29/02/2016 | x987654 | Canada | 50 |
31/03/2016 | x987654 | Canada | 305 |
30/04/2016 | x987654 | Canada | 1000 |
30/09/2016 | x987654 | Canada | 5000 |
What I would like to calculate is this:
Date | Account | Region | Amount |
31/01/2016 | n123456 | US | 10 |
31/03/2016 | n123456 | US | 15 |
31/08/2016 | n123456 | US | 45 |
30/09/2016 | n123456 | US | 1 |
29/02/2016 | x987654 | Canada | 50 |
31/03/2016 | x987654 | Canada | 255 |
30/04/2016 | x987654 | Canada | 695 |
30/09/2016 | x987654 | Canada | 4000 |
Is there a nice way for me to achieve this using DAX/Power Query ?
Thanks
@amitchandak @Pragati11
Solved! Go to Solution.
Hi @Adam_cordell_ma ,
First thing whihc is very important, do add the sample data in such a format that anyone can copy and paste it in Power BI easily. Best way of doing it is sharing it in a csv file and uploading it to dropbox and then sharing the dropbox link here.
Also, I am not sure your output sample is right. The highlighted row should return 21 not 50. Because the calculation is previous row amount subtracted from current row AMOUNT. Please confirm this.
If my understanding is right on your question, You can try something as below.
Fisrt I created an INDEX column on your sample data:
Then I created the following DAX caluclation:
CalcAmount =
VAR currentIndex =
MAX ( testTable[Index] )
VAR currentAmt =
SUM ( testTable[Amount] )
RETURN
ABS (
currentAmt
- (
CALCULATE (
SUM ( testTable[Amount] ),
FILTER ( ALL ( testTable ), testTable[Index] = currentIndex - 1 )
)
)
)
The output is shown as below:
The output displays an ABSOLUTE value.
Thanks,
Pragati
Hi @Adam_cordell_ma ,
First thing whihc is very important, do add the sample data in such a format that anyone can copy and paste it in Power BI easily. Best way of doing it is sharing it in a csv file and uploading it to dropbox and then sharing the dropbox link here.
Also, I am not sure your output sample is right. The highlighted row should return 21 not 50. Because the calculation is previous row amount subtracted from current row AMOUNT. Please confirm this.
If my understanding is right on your question, You can try something as below.
Fisrt I created an INDEX column on your sample data:
Then I created the following DAX caluclation:
CalcAmount =
VAR currentIndex =
MAX ( testTable[Index] )
VAR currentAmt =
SUM ( testTable[Amount] )
RETURN
ABS (
currentAmt
- (
CALCULATE (
SUM ( testTable[Amount] ),
FILTER ( ALL ( testTable ), testTable[Index] = currentIndex - 1 )
)
)
)
The output is shown as below:
The output displays an ABSOLUTE value.
Thanks,
Pragati
You can try this in blank query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc69DoAgDATgd2E26bVQkNlHME7GgcSZ2ccXfydgaW740rt1NZYJTAL2ZjCZxTq90jKXwzDb8BDbIKI/GRskvF9AiC3CN5FIkI8ccQxeXUlTymlPJWhlT4VZ6N/oOo6ByrRq7wsRiHsDww23Ew==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Region = _t, Amount = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Amount", type number }}),
GR = Table.RemoveColumns( Table.AddIndexColumn(Table.Group(ChangeType, {"Account","Region"}, {{"Gr", each _, type table }}),"Index"),{"Account", "Region"}),
#"Expanded Gr" = Table.ExpandTableColumn(GR, "Gr", {"Date", "Account", "Region", "Amount"}),
Index = List.Buffer(#"Expanded Gr"[Index]),
Amounts = List.Buffer(ChangeType[Amount]),
ReversedRT = List.Generate( () => [RRT = Amounts{0}, i=0],
each [i] < List.Count(Amounts),
each try if Index{[i]}= Index{[i]+1}
then [RRT = Amounts{[i]+1} - Amounts{[i]}, i = [i]+1]
else [RRT = Amounts{[i]+1}, i = [i]+1]
otherwise [i = [i]+1],
each [RRT]),
FINAL = Table.FromColumns({ChangeType[Date],ChangeType[Account],ChangeType[Region],ChangeType[Amount],ReversedRT}, {"Date","Account","Region","Amount","RRT"})
in
FINAL