我有一個列名稱日期、產品名稱、客戶名稱、銷售價值、基于產品名稱和日期的銷售價值總和、銷售百分比，即銷售價值/銷售價值總和。我想要的是像= if（產品名稱=產品名稱，銷售額百分比比方說F1 F2，F1）這樣的功能
我需要使用 Dax 或 power query 將附加影像中的最后一列重新創建到 power bi
uj5u.com熱心網友回復：
在 powerquery/M 中，您要查找的是按日期和產品型別分組后銷售百分比列的累積總和
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"
然后，您可以使用頂部的箭頭將 Rank 列過濾到您想要的任何范圍，例如 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"
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/473445.html
標籤：擅长 力比 达克斯 电源查询 powerbi-桌面