cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
guggu74 Frequent Visitor
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])))

 

Can someone please help

1 ACCEPTED SOLUTION

Accepted Solutions
Jessica_Seiya Established Member
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 ) )
)

2018-10-09_14-25-52.jpg

4 REPLIES 4

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

guggu74 Frequent Visitor
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.

Jessica_Seiya Established Member
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 ) )
)

2018-10-09_14-25-52.jpg

guggu74 Frequent Visitor
Frequent Visitor

Re: Summarize two columns

Thanks.

That's perfect