cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KL718 Frequent Visitor
Frequent Visitor

Cumulative Total

I want to calculate cumulative total. I have gone through other post but my dataset do not have Dates column.

 

Cumulative.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cumulative Total

Hi @KL718 

 

1. Create and Index column using Edit query starting from 1.

 

2.  Go through the link

       https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/

 

3. Use the Index as the column for Earlier function.

 

Cheers

 

CheenuSing

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
3 REPLIES 3
hnguy71 Member
Member

Re: Cumulative Total

You can achieve this via Power Query:

 

The meat of the code works starting from GroupMe and all the way down:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNAASSrE6QK4RkGWE4BoDWcYQbiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, Sales = _t, dd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Sales", Int64.Type}}),
    GroupMe = Table.AddColumn(#"Changed Type", "GroupMe", each 1),
    #"Added Index" = Table.AddIndexColumn(GroupMe, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"GroupMe"}, {{"All_Rows", each _, type table}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "All_Rows", "All_Rows - Copy"),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Duplicated Column", "All_Rows", {"Part", "Sales", "Index"}, {"Part", "Sales", "Index"}),
    #"Expanded All_Rows - Copy" = Table.ExpandTableColumn(#"Expanded All_Rows", "All_Rows - Copy", {"Part", "Sales", "Index"}, {"Part.1", "Sales.1", "Index.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All_Rows - Copy", "Find_Cumulative", each [Index] >= [Index.1]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Find_Cumulative] = true)),
    #"Regroup for Cumulative" = Table.Group(#"Filtered Rows", {"GroupMe", "Sales", "Index", "Part"}, {{"Cumulative Total", each List.Sum([Sales.1]), type number}})
in
    #"Regroup for Cumulative"

 

 

KL718 Frequent Visitor
Frequent Visitor

Re: Cumulative Total

Thanks.

 

Can do same with DAX?

Super User
Super User

Re: Cumulative Total

Hi @KL718 

 

1. Create and Index column using Edit query starting from 1.

 

2.  Go through the link

       https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/

 

3. Use the Index as the column for Earlier function.

 

Cheers

 

CheenuSing

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!