Sum of Revenue for values which exist in multiple columns

[rkaushik]

Hello all,

I am trying to calulate revenue for users which exist in multiple columns as indicated in the image below. For example, for user "Paul Wright" what I am trying to calculate is that the total should be sum of first 2 rows and sum of last 2 rows because he exists in first 2 rows in column 1 and last 2 rows in column 2.

How can I creat a calculation which is able to do this? Any help would be really appreciated. Thanks in advance

[Greg_Deckler]

@rkaushik Would probably be best to unpivot your data, but you could create a measure like this:

Measure =
  VAR __User = MAX('Table'[User])
  VAR __Table = FILTER('Table',[User] = __User || [User1] = __User || [User3] = __User)
RETURN
  SUMX(__Table,[Revenue])

[rkaushik]

Hi @Greg_Deckler , thanks for your solution.

I tried implementing it but the total for each user hasn't changed. It is still displaying the numbers as it was previously. 

What I am trying to create is a table where I can show each user and their respective total. 
Also, just wanted to confirm if [user] = column 1, [user1] = column2 and [user3]  = column3?

[Greg_Deckler]

@rkaushik I used [user], [user1] and [user2] as the column names. Since you didn't provide them, I made them up. Post sample data as text, including the column header names and I'll get you a working file/calculation.

[rkaushik]

@Greg_Deckler Got it.

Here's a sample data that I'm working with: 

User1	User2	User3	Revenue
Paul Wright			2000
Paul Wright	Bill		200
Roslyn	David	Bill	8200
Roslyn	Paul Wright	Bill	600
Roslyn	Paul Wright		1500

I am trying to create a table that could showcase this:

User	Revenue
Paul Wright	4300
Roslyn	10300
David	8200
Bill	9000

Let me know if I can provide more information. Thanks again for your help.

[Greg_Deckler · Accepted solution]

@rkaushik OK, I did this 2 different ways in the PBIX attached below signature. First, I created a disconnected Users table using this formula:

Users = DISTINCT(FILTER(UNION(DISTINCT('Table'[User1]),DISTINCT('Table'[User2]),DISTINCT('Table'[User3])),[User1]<>BLANK()))

And this measure:

Measure = 
    VAR __User = MAX('Users'[User1])
    VAR __Table = FILTER('Table', [User1] = __User || [User2] = __User || [User3] = __User)
RETURN
    SUMX(__Table,[Revenue])

I also created a version of the table where I unpivoted the user columns and then you don't even need a measure, just a simple sum:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgszVEIL8pMzyhR0lFSgGIjAwMDpVgddGmnzJwchAqwgqD84pzKPKCAS2JZZgpCjQWGAqxGmeFXBbLJ0BSkJhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User1 = _t, User2 = _t, User3 = _t, Revenue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User1", type text}, {"User2", type text}, {"User3", type text}, {"Revenue", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Revenue"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> " "))
in
    #"Filtered Rows"

[rkaushik]

@Greg_Deckler While the first solution you suggested didn't work, the unpivot solution worked perfectly. Thanks for your help.