Summarize all (except one) columns via measure

[marcel97]

Hello all,

I have the following table showing the size of project teams in my matrix organisation. It shows for every project and month, how many people from which org. team and region have been working on it:

REFERENCE_DATE	TEAM_ID	PROJECT_ID	REGION_ID	REFERENCE_SIZE
May 22	1	1	1	3
May 22	1	1	2	4
May 22	1	2	1	5
May 22	1	2	2	6

For a measure I need this table without the info on the region, so something like this:

REFERENCE_DATE	TEAM_ID	PROJECT_ID	REFERENCE_SIZE
May 22	1	1	7
May 22	1	2	11

With SUMMARIZECOLUMNS() this is quite easy, but please not that my real data has a few more columns than the few shown here, making this a bit unpractical.

I tried to make something work with ALLEXCEPT() but failed...

Is there a simple way to do it without having to specify every single (except for one) column of the table?

Thanks in advance!

Marcel

[v-yanjiang-msft · Accepted solution]

Hi @marcel97 ,

According to your description, if you want to create a new table with all columns except one particular column, no need to use SUMMARIZE, just with ALLEXCEPT. Here's my solution.

Table 2 = ALLEXCEPT('Table','Table'[REGION_ID])

Then set the Team ID and Project ID to "Don't summarize", and Reference_size to "Sum".

It will get the expected result in the visual.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.