COUNTROWS with ALL and FILTER

[joshua1990]

Hello everybody!

I have a table with the following structure linked to my data model:

Item	Date	Value
A	01.03.2020	+550
A	02.03.2020	-600

The Date Column is an active connection to the calendar table.

Then I have a Pivot table like this:

Week	Value
2020-01	+5000
2020-01	-50

This table shows me the sum for each week.

Now I want a second column in this pivot that shows me the total sum regardless of the week, but just for values above 500.

The following measure is not working:

=CALCULATE(COUNTROWS(tblOne);FILTER(tblOne;tblOne[Value]>500);ALL(tblOne))

=CALCULATE(COUNTROWS(tblOne);ALL(tblOne);FILTER(tblOne;tblOne[Value]>500))

How would you solve that?

[Anonymous]

Or... you could try this as well:

[GT500 Total] =
CALCULATE(
	SUM( Table[Value] ),
	Table[Value] > 500,
	ALL( CalendarTable )
)

This one will respect all filters in all dimensions but the ones on CalendarTable. Maybe this is what you wanted?

Best

D

[Greg_Deckler · Accepted solution]

Perhaps:

Measure =
  SUMX(FILTER(ALL('Table'),[Value]>500),[Value])

Although I can't fathom why you were using COUNTROWS so if you actually want the count, use COUNTX instead of SUMX.