cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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
Highlighted
Solution Sage
Solution Sage

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

View solution in original post

4 REPLIES 4
Highlighted
Anonymous
Not applicable

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

Highlighted
Regular 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.

Highlighted
Solution Sage
Solution Sage

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

View solution in original post

Highlighted
Regular Visitor

Re: Summarize two columns

Thanks.

That's perfect 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors