cancel
Showing results for
Did you mean:
Frequent Visitor

## Summarize two columns

Hi,

I have looked through many forums including this one but can not find a solution.

Issue:

I have a table with many rows and I have used bar charts and and a matrix to represent the data.

What I need to do is subtract the counting of rows for each.

In the table I have many columns but only need two.

Activity           Year

Activity 1         2012

Activity 1         2014

Activity 2         2012

Activity 1         2014

Activity 1         2012

Activity 2         2014

Activity 1         2012

Activity 2         2014

I have summarised these as such and even summarised in a distinct table but can not get the totals

Activity           2012         2014

Activity 1         3                2

Activity 2         1                2

What I'm trying to do is find a way I can subtract the two.

I have tried some DAX via forums and google but can not find the solution.

Some of what I have tried are:

NewTable = SUMMARIZECOLUMNS('Table'[Activity],'Table'[Year])

NewMeasure = sumx(VALUES('Table'[Year]),CALCULATE(count('Table'[Activity])))

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Summarize two columns

You can try the following DAX

```New Table =
SUMMARIZE (
Activity,
Activity[Activity],
"2012", COUNTROWS ( FILTER ( Activity, Activity[Year] = 2012 ) ),
"2013", COUNTROWS ( FILTER ( Activity, Activity[Year] = 2014 ) )
)```

4 REPLIES 4
Member

## Re: Summarize two columns

You have to keep a "count" column in the primary table, something like this:
ThisColumnKeepsCount = IF(ISNUMBER(Table1[Year]),1,0)

Then,
NewTable = SUMMARIZECOLUMNS(Table1[Activity],Table1[Year],"AppropriateColumnName",SUM(Table1[ThisColumnKeepsCount]))

Frequent Visitor

## Re: Summarize two columns

Thanks for that, but that didn't work. I got zeros in the original table for everything

I managed to get a new table with the following

NewTable=SUMMARIZE(
'Table',
'Table'[Activity],
'Table'[Year]
"Count", COUNT('Table'[Activity])
)

But now I need to group each activity (by Row) and display the year and total for each and the subtract the two to get a difference.

Established Member

## Re: Summarize two columns

You can try the following DAX

```New Table =
SUMMARIZE (
Activity,
Activity[Activity],
"2012", COUNTROWS ( FILTER ( Activity, Activity[Year] = 2012 ) ),
"2013", COUNTROWS ( FILTER ( Activity, Activity[Year] = 2014 ) )
)```

Frequent Visitor

Thanks.

That's perfect