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

Highlighted
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 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 312 members 3,113 guests
Please welcome our newest community members: