委员会拆分计算PowerBi/Excel/Python/Tableau

发布于 2025-01-30 08:59:37 字数 537 浏览 1 评论 0原文

我需要从所示的导出数据中创建一个措施，有时总计将是一位代表（即凯特·皮尔森）的1000美元，而其他时候，它将分配给两个人（Kate和Randal，每个人都有500美元）三。

它与Python中的Argmax有点相似，但是我无法弄清楚以这种方式提取数据的计算。

我想要的是每个机会，1个或更多的人的总数清单，如果不止一个，则与负责人一起列出总数。

如果有人很乐意在Python代码甚至Excel或PowerQuery中分享他们的逻辑，那将是一个很棒的起点。

我想实现的示例：

Totals:

Randal total: 1750
Kate Total: 2100
Kevin Total: 1150

原文

I need to create a measure from the pictured exported data, where sometimes the Total will be e.g. $1000 from one representative (i.e. Kate Pearson) and at other times it will be split between two people (Kate and Randal, each have $500) or even three.

It is somewhat similar to argmax in Python, but I cannot figure out what calculation to use to extract the data in this manner.

What I want is for each opportunity, a list of totals by either 1 or more people, and if more than one, to list the total along with the person who was responsible.

If anyone is happy to share their logic in python code or even Excel or PowerQuery, that will be a great starting point.

Example of output I want to achieve:

Totals:

Randal total: 1750
Kate Total: 2100
Kevin Total: 1150

分享到QQ

分享到微博

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

发布评论

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

深海夜未眠 2025-02-06 08:59:37

您可以在电源查询（home =＆gt; transform）中执行此操作

。如果不进行以下代码的source行中的更改。

该算法在代码注释中概述。但还探索了应用的步骤以查看每个步骤在做什么：

let
    Source = #"Sales Table",

//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(Source,{"OpportunityID", "Total Sales"}),

//Unpivot all the columns and remove the Attribute column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),

//We now have pairs of Rep and Split in a single column
// So add an index to group the pairs
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),

//Aggregate by creating a two column table
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Integer-Division"}, {
        {"all", each [rep=[Value]{0}, Split=[Value]{1}] }}),
    #"Expanded all" = Table.ExpandRecordColumn(#"Grouped Rows", "all", {"rep", "Split"}, {"rep", "Split"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded all",{"Integer-Division"}),

//Remove the "NA" reps
//then group by Rep and aggregate by Sum
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns3", each ([rep] <> "NA")),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"rep"}, {{"Rep Total", each List.Sum([Split]), type number}})
in
    #"Grouped Rows1"

You could do this in Power Query (Home=>Transform)

I assume your above table name is Sales Table. If not make the change in the Source line of the below code.

The algorithm is outlined in the code comments. But also explore the applied steps to see what each step is doing:

let
    Source = #"Sales Table",

//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(Source,{"OpportunityID", "Total Sales"}),

//Unpivot all the columns and remove the Attribute column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),

//We now have pairs of Rep and Split in a single column
// So add an index to group the pairs
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),

//Aggregate by creating a two column table
    #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Integer-Division"}, {
        {"all", each [rep=[Value]{0}, Split=[Value]{1}] }}),
    #"Expanded all" = Table.ExpandRecordColumn(#"Grouped Rows", "all", {"rep", "Split"}, {"rep", "Split"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded all",{"Integer-Division"}),

//Remove the "NA" reps
//then group by Rep and aggregate by Sum
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns3", each ([rep] <> "NA")),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"rep"}, {{"Rep Total", each List.Sum([Split]), type number}})
in
    #"Grouped Rows1"