Look up a value and multiply it to a value in a separate table to obtain a total

[BeeBop]

Hi - I have two tables that I am using to calculate productivity.  Both are connected to lists.

Table one has headers with task names and volumes processed per person (one row per person).

Table two has the same column headers and in row one it has the time that that task takes in minutes.

Currently I have an excel sheet that does this but sharepoint can be a bit flaky with the vba and the code, so I am trying to do the same thing in lists (as an entry point - maybe looking to power apps once I get it going) and then do the calculation bits in Power BI.

In the current excel sheet I have a column with a big formula that looks up the duration value of each task and multiplies it by the volume entered by each person per day and then adds all of the task times together to create a subtotal of core time.

So in the above example the core time equals (Column 1*Column 1)+(Column 2*Column 2)+(Column 3*Column 3) etc.

Can this be done in Power BI - are my tables set up correctly to do this or is it just not possible?

Thanks in advance for the help!

[Anonymous]

Hi @BeeBop ,

Please find the details in the attachment.

Best Regards

[Anonymous · Accepted solution]

Hi @BeeBop ,

I created a sample pbix file(see the attachment), please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVQ0lEyNAUSRhBGbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Batching Team Members" = _t, #"Agency Transfers" = _t, #"Backdated Renewals" = _t, #"Bank Rec/Petty Cash" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Batching Team Members", Int64.Type}, {"Agency Transfers", Int64.Type}, {"Backdated Renewals", Int64.Type}, {"Bank Rec/Petty Cash", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Batching Team Members", "Agency Transfers", "Backdated Renewals", "Bank Rec/Petty Cash"}, "Task Name", "Consumed Time"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Only Selected Columns", {"Task Name"}, Table1, {"Task Name"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"TasK Volume"}, {"TasK Volume"})
in
    #"Expanded Table1"

Unpivot the columns and merge the tables in Power Query Editor

Create a calculated column

Best Regards