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.

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

4 Replies

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

      Thanks.

      That's perfect 

  • 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.