Cumulative sum of grouped columns in Powerquery, PowerBI, Dax-CodePudding

I have a column name date, product name, customer name, sale value, sum of sale value based on product name and date, percentage of sales which is sale value / sum of sale value. What i want is function like = if(product name = product name, percentage of sales let's say F1 F2, F1)

I need to recreate the last column in the attached image into power bi using Dax or power query

CodePudding user response：

In powerquery/M what you are looking for is a cumulative sum of the sale% column after grouping on date and Product Type

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Product type", type text}, {"cm name", type text}, {"sales", type number}, {"sum of sale", type number}, {"sale%", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"date", "Product type"}, {
    {"data", each 
    let x=Table.AddIndexColumn(_, "Index", 1, 1) ,
    y=Table.AddColumn(x, "Rank", each List.Sum(List.FirstN(x[#"sale%"],[Index])))
    in y , type table [date=nullable date, Product type=nullable text, cm name=nullable text, sales=nullable number, sum of sale=nullable number, #"sale%"=nullable Percentage.Type, Rank=nullable Percentage.Type]}
    }),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"cm name", "sales", "sum of sale", "sale%", "Rank"}, {"cm name", "sales", "sum of sale", "sale%", "Rank"})
in #"Expanded data"

You can then filter the Rank column using the arrows atop it to any range you want, like 75%

====

You could also generate your three extra columns at once as part of the group on the first 4 columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"date", "Product type"}, {
    {"sum of sale", each List.Sum([sales]), type number}, 
    {"data", each 
    let x=Table.AddIndexColumn( Table.AddColumn(_, "sale%", each [sales]/List.Sum(Source[sales])), "Index", 1, 1) ,
    y=Table.AddColumn(x, "Rank", each List.Sum(List.FirstN(x[#"sale%"],[Index])))
    in y , type table [date=nullable date, Product type=nullable text, cm name=nullable text, sales=nullable number, sum of sale=nullable number, #"sale%"=nullable Percentage.Type, Rank=nullable Percentage.Type]}
    }),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"cm name", "sales", "sale%", "Rank"}, {"cm name", "sales", "sale%", "Rank"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data",{{"date", type date}, {"Product type", type text}, {"sum of sale", type number}, {"sales", type number}, {"sale%", Percentage.Type}, {"Rank", Percentage.Type}, {"cm name", type text}})
in  #"Changed Type"