Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Weighting Data based on certain values in a column

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 Equi...
Need Help
  • hashtag_pete's avatar
    hashtag_pete
    3 years ago

    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

hashtag_pete's avatar
hashtag_pete
Helper V
3 years ago

 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's avatar
Anonymous
Not applicable
3 years ago

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