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 coulmn with decimal values, and I want to create a calculated column that will be the cumulative of that column. For example:
Column 1 cumulative
1,65 1,65
1,65 3,3
0,33 3,63
0,66 4,29
Any tips on how to accomplish this? Thanks!
Solved! Go to Solution.
Hi @Anonymous,
If your table contains index column, it is possible to get the running total based on index column.
Sample: calculate column
Running Total = SUMX ( FILTER ( ALL ( Test ), Test[Index] <= EARLIER ( Test[Index] ) ), [Value] )
Regards,
Xiaoxin Sheng
Hi @Anonymous,
If your table contains index column, it is possible to get the running total based on index column.
Sample: calculate column
Running Total = SUMX ( FILTER ( ALL ( Test ), Test[Index] <= EARLIER ( Test[Index] ) ), [Value] )
Regards,
Xiaoxin Sheng
A Power Query solution would be:
let #"**** QUERY SUMMARY ***" = "This query adds a column with the accumulation of the value in column ""Column 1"".#(lf)" & "A buffered list is created from the values.#(lf)" & "Using List.Accumulate, the values are accumulated.#(lf)" & "The initial value (the ""seed"" parameter of List.Accumulate) is a list with 1 item: {0}#(lf)" & "With each iteration, the value (v) is added to the last item of the cumulated list (c) and#(lf)" & "the result is concatenated to the cumulated list.#(lf)" & "After accumulation, the first element {0} is skipped.#(lf)" & "The resulting list is added to the table by transforming the table into columns (a list of lists),#(lf)" & "the list of accumulated values is added to this list of lists,#(lf)" & "the result is transformed to a table (using Table.FromColumns).#(lf)" & "This table gets the table type from the Source Table, to which the column ""Cumulative"" is added with dummy value 1:#(lf)" & "that additional column is only required for the column name and column type.", Source = Table1, NumberList = List.Buffer(Source[Column 1]), Cumulative = List.Skip(List.Accumulate(NumberList,{0},(c,v) => c & {(List.Last(c)+v)})), AddCumulativeToSource = Table.FromColumns(Table.ToColumns(Source)&{Cumulative}, Value.Type(Table.AddColumn(Source,"Cumulative", each 1, type number))) in AddCumulativeToSource
Thank you, @MarcelBeug. I'm still learning power query language, so your code is a bit difficult for me, but thanks.
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |