Sum values per user where user can be in multiple column

[r_ramillien]

Dear Power BI community,

I'm facing an issue where I have simple table with columns "amount" and "user1", "user2", "user3", etc.

The same user can be in any of this columns.

I would like to have a visual with the total of each user.

So for instance, if my source table contains:

25 userA userB userC

50 userB null null

75 userA UserB

I would like a visual like this:

userA 100

userB 150

userC 25

Do you think it is possible with DAX, or with prior transformation in powerQuery ?

Thanks for all your kind responses 😁

[mahoneypat · Accepted solution]

You could do it with DAX and the SEARCH function but it is much better to unpivot your data in the query editor.  Highlight the Amount column, right click and choose unpivot other columns.  Do any other clean up that is necessary and then you should have two columns with Users and Amounts you can easily sum by user in your visuals.

Regards,

Pat

[StefanoGrimaldi · Accepted solution]

well them you need to tranform the data, thats not a friendly table for dataset, so go over to your power bi query editor, elect all columns that are not the users columns xD and use the unpivot function of the software to make all user be in one unique column you will have something like this: 

now you go over to your report section with that tranformed table and just put those 2 fields on a normal table visual you will get this result: 

sso basically the missinsg and important step here its your table tranformation for a nice clean table, 

if this solved your question give some kudos and mark it as solution for others to find it more easily.

[r_ramillien]

Thank you for your help,

I made some tests and it works great !

[mahoneypat · Accepted solution]

You could do it with DAX and the SEARCH function but it is much better to unpivot your data in the query editor.  Highlight the Amount column, right click and choose unpivot other columns.  Do any other clean up that is necessary and then you should have two columns with Users and Amounts you can easily sum by user in your visuals.

Regards,

Pat

[StefanoGrimaldi]

hey,

question here, you have a column per each user? or 2 columns: amount, user id? 

[r_ramillien]

Hello,
I have a column for the amount and a defined number of columns for users (lets say 3 columns for example).
In each of these 3 columns, I have any user name.

For example:

Amount | user1 | user2 | user3

25          | userA |           | userB

4            | userB | userA |

52          | userX | userP | userB

In the real table, I have 11 columns for users and hundreds of differents user.

Thanks Stefano for you help,