Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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. ...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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.