Calculate Weight of Each Product by Employee ID

[subirch]

Hi,

I have a table with employee ID, product name along with total, target of products they are selling as depicted in table. How can I calculate weight for each product line for each employee. Obviously the the weight of "Total" would be always 100%. I need the solution using M not in DAX. 

EMP Id	Product Lines	Targets	Weight
788788	A	1,200,000
788788	B	20,000,000
788788	Total	21,200,000
788789	A	9,581,720
788789	B	6,453,456
788789	C	17,400,000
788789	Total	33,435,176

[subirch]

Hi,

I have a table with employee ID, product name along with total, target of products they are selling as depicted in table. How can I calculate weight for each product line for each employee. Obviously the the weight of "Total" would be always 100%. I need the solution using M not in DAX. 

EMP Id	Product Lines	Targets	Weight
788788	A	1,200,000
788788	B	20,000,000
788788	Total	21,200,000
788789	A	9,581,720
788789	B	6,453,456
788789	C	17,400,000
788789	Total	33,435,176

[ronrsnfld]

• Group by ID
• In the Aggregation, calculate the weights by which I believe you mean the Percentage of each target as compared with the Total
• Change the "Source = " line to reflect yourd actual data source
  • The Data Source should NOT include the blank Weight column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMP Id", Int64.Type}, {"Product Lines", type text}, {"Targets", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EMP Id"}, {
        {"Wt", (t)=>Table.AddColumn(t,"Weight", each [Targets] / List.Last(t[Targets])), 
                    type table[Product Lines=text, Targets=Int64.Type, Weight=Percentage.Type]}       
            }),
    #"Expanded Wt" = Table.ExpandTableColumn(#"Grouped Rows", "Wt", {"Product Lines", "Targets", "Weight"})
in
    #"Expanded Wt"

[ChielFaber]

You can create a new column using the m function List.Sum. That will provide you with a grandtotal value in each row. You can then create a new column where you calculate the weight.

Take a look at the following youtube video to get an idea how to use List.Sum:

https://www.youtube.com/watch?v=Mud1RSqW5pw