Complex data calculation with IF and SUM conditions

[Anonymous]

Hello, I have such a complex task.

My SharePoint database consists of the following data:

ID	Is this a project?	Registered	Executor	Hours saved	Team
1	No	Antonio	Damon	6
2	No	Billy	Damon	10
3	Yes	Antonio	Erick	40	Billy, Erick, George
4	No	Cecilia	Faust	2
5	No	Ceclia	George	4

Depending on whether it’s a project or not, I need to calculate how many hours each employee in the company saves.

The calculation must be made under the following conditions:

1. If "Is this a project? = Yes" - must be calculated:
1.1. How many team members are in the project (including the project manager, project manager in my table is "Executor"),
1.2. Divide the saved project hours by the number of teams,
1.3. Allocate those hours to each of them.

The result would be:

Antonio	10
Billy	10
Erick	10
George	10

2. If "Is this a project? = No" - must be calculated:

2.1. If a person has registered, that person will be assigned 30 percent of the hours saved.
2.2. If a person is an executor, then that person is assigned 70 percent of the hours saved.

The result would be:

Antonio	1,8	Example: 6*0,3
Billy	3	Example: 10*0,3
Cecilia	1,8	Example: 2*0,3+4*0,3
Damon	11,2	Example: 6*0,7+10*0,7
Faust	1,4	Example: 2*0,7
George	2,8	Example: 4*0,7

Having calculated the data collected by employees, I need to summarize them. The final result should be as follows:

Antonio	11,8
Billy	13
Cecilia	1,8
Damon	11,2
Erick	10
Faust	1,4
George	12,8

I'm not a professional, I'm just learning this tool, so I can't solve such a complex task with Power BI. Could you help?

[TomMartens · Accepted solution]

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

[v-alq-msft · Accepted solution]

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.

[Anonymous]

@v-alq-msft Thank you, but I have some problems because the "Team" field is not stored in the first table by default. I load data from a SharePoint LIST where there is a "Team" field (type: People and Groups). After loading data from this field into Power BI, it doesn't show me team members. Also, a field like TeamID is loaded at the same time. In this case, I need to link the SharePoint TeamID to another source Sharepoint UserInformationList (UserID) (from https://sharepointsitename.sharepoint.com/_vti_bin/listdata.svc
). After that, I see team members.
I can't provide real data, but I'm uploading a picture with an example.

In this case, would it be possible to load that data into the first table, or would your code need to be slightly modified?

[v-alq-msft]

Hi, @Anonymous 

If i understand correctly, you may add a custom column in the first table to get the corresponding 'Team' field value from the second table by 'ID' column. I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

You may add a custom column in the first table with the following m codes.

= Table.AddColumn(#"Changed Type", "Custom", each if [#"Is this a project?"]="Yes"
then Text.Combine(
    Table.SelectRows(Table2,(x)=>x[TeamID]=[ID])[Team],
    ","
)
else ""
)

Result:

Best Regards

Allan

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

[TomMartens · Accepted solution]

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

[lbendlin]

You have chosen a rather steep challenge for learning Power BI. Good luck 🙂

The general approach would be to first generate a list of all people. Ideally you have that as an external table , but it would also be possible to generate it as a calculated table from the project data

Then you need to iterate through all projects for each of your users.

- identify in what role the user is participating in the project (if any)

- for the "no" case - apply the 30/70 rule

- for the yes case you need to DISTINCT(UNION(Requested,Executor,(exploded Team)) to arrive at the number of people to share the hours with

It's doable, but certainly not something for beginners.

Let me know if you want to see a sample implementation.

[Anonymous]

Thanks, but such an explanation will definitely not help for a beginner. 😀

I have a external table of people. 

What do you mean: "Then you need to iterate through all projects for each of your users."?

Maybe you can give an example?