Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 ) ) )
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 ) ) )
Thanks.
That's perfect
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]))
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |