Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Creating a sum based on 3 columns

I have a table with unique ID's for 50 project owners. However each owner has mutiple rows (1 for each project). Each project owner can have up to 3 supporting engineers.

 

What I am trying to do is make a visual showing workload. Meaning how many projects am I the owner to, and how many am I supporting.

I believe the answer lies in creating a table without duplicate entries for each engineer, and then a sum of the 3 eng support columns.

 

Is there a way to take 3 columns, and do a sum for how many times does my name appear in any 3 columns, and display 1 sum to each unique owner name?

 

An example to what I'm trying to manipulate is below.

 

Owner      Eng Support1    Eng Support2  Eng Support 3

Marcus        Dolan            

Marcus        Federico

Marcus

Dolan           Marcus              Federico

Federico

 

I am trying to make a new table that would show this

                     Owner               Supporting

Marcus              3                            1

Federico            1                            2

Dolan                1                            1

1 ACCEPTED SOLUTION
rohit_singh
Solution Sage
Solution Sage

Hi @Anonymous ,

Please try this : 

1) Load data into Power Query

rohit_singh_0-1652715056819.png

2) Unpivot all columns

rohit_singh_1-1652715084684.png


3) Add custom columns for "Owner" and "Attribute"

rohit_singh_2-1652715135475.png


4) Group rows on field "value" and calculate sum of "Owner" and "Supporting"

rohit_singh_3-1652715183420.png

5) Filter blank values to get the desired result

rohit_singh_4-1652715210363.png

Here is the code for your reference :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sSi4tVtJRcsnPScwD0iAUq4Mk4ZaaklqUmZyPTQ5JDKYfqz6QAjRzQKKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Owner = _t, #"Eng Support1" = _t, #"Eng Support2" = _t, #"Eng Support 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Owner", type text}, {"Eng Support1", type text}, {"Eng Support2", type text}, {"Eng Support 3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Owner", each if [Attribute] = "Owner" then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Supporting", each if Text.Contains([Attribute], "Support") then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Value"}, {{"Owner", each List.Sum([Owner]), type number}, {"Supporting", each List.Sum([Supporting]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value] <> ""))
in
#"Filtered Rows"

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

View solution in original post

3 REPLIES 3
rohit_singh
Solution Sage
Solution Sage

Hi @Anonymous ,

Please try this : 

1) Load data into Power Query

rohit_singh_0-1652715056819.png

2) Unpivot all columns

rohit_singh_1-1652715084684.png


3) Add custom columns for "Owner" and "Attribute"

rohit_singh_2-1652715135475.png


4) Group rows on field "value" and calculate sum of "Owner" and "Supporting"

rohit_singh_3-1652715183420.png

5) Filter blank values to get the desired result

rohit_singh_4-1652715210363.png

Here is the code for your reference :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sSi4tVtJRcsnPScwD0iAUq4Mk4ZaaklqUmZyPTQ5JDKYfqz6QAjRzQKKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Owner = _t, #"Eng Support1" = _t, #"Eng Support2" = _t, #"Eng Support 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Owner", type text}, {"Eng Support1", type text}, {"Eng Support2", type text}, {"Eng Support 3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Owner", each if [Attribute] = "Owner" then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Supporting", each if Text.Contains([Attribute], "Support") then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Value"}, {{"Owner", each List.Sum([Owner]), type number}, {"Supporting", each List.Sum([Supporting]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value] <> ""))
in
#"Filtered Rows"

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

Anonymous
Not applicable

Thank you so much for your help!

 

I have an additional question.

 

So, if I needed to add another column to that.

 

If I every project had a priority assigned to it. Our new table could say how many projects I have that are priority 1-6, could you help me figure that out?

Hi @Anonymous ,

No worries. Please provide sample input data and expected output so that I can help you better.

Kind regards,

Rohit

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors