topic Re: Lookup a category across columns and find sum in DAX Commands and Tips

Lookup a category across columns and find sum

Anonymous — Fri, 28 Jan 2022 12:45:43 GMT

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.

Re: Lookup a category across columns and find sum

ValtteriN — Fri, 28 Jan 2022 12:55:12 GMT

Hi,

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

Re: Lookup a category across columns and find sum

Anonymous — Fri, 28 Jan 2022 12:58:45 GMT

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

Re: Lookup a category across columns and find sum

ValtteriN — Fri, 28 Jan 2022 13:15:24 GMT

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

Re: Lookup a category across columns and find sum

Anonymous — Fri, 28 Jan 2022 13:17:14 GMT

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

Re: Lookup a category across columns and find sum

ValtteriN — Fri, 28 Jan 2022 13:33:52 GMT

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?

Re: Lookup a category across columns and find sum

Anonymous — Fri, 28 Jan 2022 13:36:04 GMT

I have 47 such columns

Re: Lookup a category across columns and find sum

ValtteriN — Fri, 28 Jan 2022 14:46:04 GMT

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

Re: Lookup a category across columns and find sum

Anonymous — Sat, 29 Jan 2022 15:35:44 GMT

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?

Re: Lookup a category across columns and find sum

ValtteriN — Sat, 29 Jan 2022 16:50:09 GMT

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?

Re: Lookup a category across columns and find sum

Anonymous — Mon, 31 Jan 2022 05:51:13 GMT

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.