PowerQuery，Powerbi，DAX中的分组列的累积总和

发布于 2025-01-28 07:50:32 字数 322 浏览 2 评论 0原文

我有一个列名称日期，产品名称，客户名称，销售价值，基于产品名称和日期的销售价值，销售百分比为销售价值 /销售价值。我想要的是= = if（产品名称=产品名称，销售百分比，例如F1 + F2，F1），

我需要使用DAX或Power Query

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

记忆で 2025-02-04 07:50:32

在PowerQuery/M中，您要寻找的是 sale 列的累积总和，分组 date 和产品类型

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"

您就可以过滤使用箭头上的等级列到您想要的任何范围，例如75％

====

您也可以在第一个4列

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"

< a href =“ https://i.sstatic.net/t4uul.jpg” rel =“ nofollow noreferrer”>

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"