Complex data calculation with IF and SUM conditions

Hey Anonymous ,

here you will find a pbix file

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EVjLF2yiqXBCog3wEtfmdQABEMm03Fk7ejCH7DDCRTVIzw?e=f3NISa

that contains a sample implementation using Power Query.

If you look at the steps on the right (the first two steps have been added automatically by copying the data you provided to an empty table), this is what happens:

Here is a screenshot that shows a simple table visual:

Hopefully, this helps to tackle your challenge.

Regards,

Tom

Hi, Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Table2:

M codes in 'Advanced Editor':

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLLBxKOeSX5eZkglktibn4ekDYDYgWlWJ1oJSOYIqfMnJxKJCWGBnA1xkBWZGoxikmuRZnJ2UDaxACmVwcspOOeml+UngrWZwIz2zk1OTMnMxHIckssLS4B0kZww00xFUGN0AEbAFQVCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Is this a project?" = _t, Registered = _t, Executor = _t, #"Hours saved" = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Is this a project?", type text}, {"Registered", type text}, {"Executor", type text}, {"Hours saved", Int64.Type}, {"Team", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Is this a project?"] = "Yes")),
    Custom1 = Table.TransformColumns(#"Filtered Rows",{"Team",each Text.Split(_,",")}),
    #"Added Custom" = Table.AddColumn(Custom1, "Name", each List.Union({[Team],{[Registered]}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each [Hours saved]/List.Count([Name])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ID", "Is this a project?", "Registered", "Executor", "Team", "Hours saved"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Name")
in
    #"Expanded Custom"

Table3:

M codes in 'Advanced Editor':

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLLBxKOeSX5eZkglktibn4ekDYDYgWlWJ1oJSOYIqfMnJxKJCWGBnA1xkBWZGoxikmuRZnJ2UDaxACmV0cBLKaj4J6aX5SeCtZpAjPdOTU5MyczEchySywtLgHSRnDjTTEVQY3QARsAVBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Is this a project?" = _t, Registered = _t, Executor = _t, #"Hours saved" = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Is this a project?", type text}, {"Registered", type text}, {"Executor", type text}, {"Hours saved", Int64.Type}, {"Team", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Is this a project?"] = "No")),
    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Registered"}, {{"Result", each List.Sum([Hours saved])*0.3, type nullable number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows1",{{"Registered", "Name"}}),
    #"Grouped Rows2" = Table.Group(#"Filtered Rows", {"Executor"}, {{"Result", each List.Sum([Hours saved])*0.7, type nullable number}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows2",{{"Executor", "Name"}}),
    Res = Table.Combine({#"Renamed Columns",#"Renamed Columns1"})
in
    Res

Table4:

M codes in 'Advanced Editor':

let
    Source = Table.Combine({#"Table 2",#"Table 3"}),
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Result", each List.Sum([Result]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Name", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.