Weighting Data based on certain values in a column

[Anonymous]

Hi everyone,

I have a tricky situation which I have not been able to solve from reading the current forums on the Power Bi community. I have data where I need to apply a weighting of the "FTE Equivalent" column based on the "% of Hours" column.

In the custom columns I have already multiplied the FTE equivalent to the % of hours, however I will need another column where all of Payroll # 355161 would equal 1. So for example the new weighting for the first 5 rows of the new column would be 0, 0.847, 0.153, 0, 0. This would then apply to the rest of the other ID's to weight their FTE equivalent. 

Thanks in advance,
Justin

[hashtag_pete · Accepted solution]

Hello,  

please try the following code. Based on your screenshot, I have made up an example, so you might have to change the referred step "#"Changed Type":

#"Multiplication" = Table.AddColumn(#"Changed Type", "Multiplication", each [#"% of Hours"] * [FTE Equivalent], type number),
    #"Grouped rows" = Table.Group(#"Multiplication", {"Payroll"}, {{"Sum%Hours", each List.Sum([#"% of Hours"]), type nullable number}}),
    Custom1 = Table.ToRecords(#"Grouped rows"),
    Custom2 = Table.AddColumn( #"Multiplication", "result", each [Multiplication] / 
        Record.Field(List.First(List.Select(Custom1, each _ [Payroll] = 355161)), "Sum%Hours"), Int64.Type)
in
    Custom2

In case you want to divide it by the respective sum of the payroll number, you can use

   #"Multiplication" = Table.AddColumn(#"Changed Type", "Multiplication", each [#"% of Hours"] * [FTE Equivalent], type number),
    #"Grouped rows" = Table.Group(#"Multiplication", {"Payroll"}, {{"Sum%Hours", each List.Sum([#"% of Hours"]), type nullable number}}),
    SelfJoin = Table.NestedJoin(#"Multiplication", "Payroll", #"Grouped rows", "Payroll", "SumValue"),
    ExpandSelfJoin = Table.ExpandTableColumn(SelfJoin, "SumValue", {"Sum%Hours"}, {"Sum%Hours"}),
    result = Table.AddColumn(ExpandSelfJoin, "Division", each [Multiplication] / [#"Sum%Hours"], type number)
in
    result

best

hashtag_pete

[Anonymous]

Hi hashtag_pete Thanks for that! It works extremely well and is just what I was after. Regards, Justin

[hashtag_pete]

 Hello Justin, 

could you please clarify your expected outcome?

You write "for the first 5 columns would be 0, 0.847, 0.153, 0, 0" (I guess you mean rows?) but how do you calculate this?

I understodd that you take [FTE Equivalent] * [% of Hours] for the Custom column, but what would be the calculation to receive your numbers above?

Best 

hashtag_pete

[Anonymous]

Hi hashtag_pete,

Appreciate the response, my apologies I did mean rows. Those values would've come from getting the total % of hours for each Payroll ID number. So for example the sum of ID 355161 would be 80.55%, the 0.6824 would be then divided by 80.55% to equal 0.847.  As a result the new column would be the new weighting and equal to 1 in this example. Please let me know if you have any more questions! 

thanks heaps