Lookup a category across columns and find sum

[Anonymous]

I have a dataset which looks like this. Each contract has multiple tags

Contract Title	Award Value	Tag A	TAG B	TAG C
		AAAA	DDDD	CCCC
		BBBB		DDDD
		CCCC		AAAA

I want to create a table which displays the total award value across each tag. 

[ValtteriN · Accepted solution]

Hi,

I recommend unpivoting your tag columns and using a simple SUM measure. This should get yo uthe result you want.

[Anonymous]

All tags were in a single cell, so I separted them in different columns. Can you help with the dax code if possible?

[ValtteriN]

Just as a clarification: do you want to show it like TAG A: value or like AAAA: value?

[Anonymous]

AAAA value, so that I can identify the tags with the overall highest award value.

[ValtteriN]

Okay, Then there are limitations in DAX solution, since you would have to refer to each column separately. How many "Tag"-columns d oyou have?

[Anonymous]

I have 47 such columns

[ValtteriN]

Yeah, In that case the best solution would be to unpivot these columns like this:
So in PQ select the column and unpivot them. 

[Anonymous]

This was helpful! While double counting is unavoidable if I want to display the award value for tags, it is not preferable for contract title as that would be a misrepresentation. Is there a way to work around that?

[ValtteriN]

@Anonymous  I am not sure I follow. If you want to count distinct values you can include filters or use functions like DISTINCT in your measures. Now that you have unpivoted data what is your desired end result?

[Anonymous]

I plan to make two tables:

1. Contract Title and award value

2. Tags and award value

I want these two to interact with each other. But I want the Contract Title to not display duplicate values as we have unpivoted the columns.