Summarize two columns

[Anonymous]

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])))

Can someone please help

[Anonymous · Accepted solution]

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 ) )
)

[Anonymous]

Thanks.

That's perfect 

[Anonymous]

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]))

[Anonymous]

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.