count greater than 1000 (report builder)

[BeginnerBI]

I need an expression to count (identify) those amounts greater than 1000 from an existing column in powerbi paginated reports.

column (payments)

$54

$23

$40

$14

$28

table

sum of the payment = 239

count payments under $25 = 2 (this is the expression that I need)

Please your help is greatly appreciated. no experience in rdl.

@PaginatedReport 

@PaginatedDino 

[PaginatedDino · Accepted solution]

@BeginnerBI 

I suggest, if not already done, to also view an introduction into paginated reports. 

My knowledge is rusty but this should work out 😉 

I cannot easily share this file since I'm on a work laptop so if you need it mailed, dm me. 

Basically I can think of 2 options: 

- you add a (calculated) column if you have e.g. an SQL query:

WITH sales_data AS (
  SELECT 54 AS value
  UNION ALL
  SELECT 23 AS value
  UNION ALL
  SELECT 40 AS value
  UNION ALL
  SELECT 14 AS value
  UNION ALL
  SELECT 28 AS value
)
SELECT	value,
		case 
			when value < 25 
			then 1 
			else 0 
		end as paymentsUnderBorder FROM sales_data;

Then you can sum up the occurences with an expression: =Sum(Fields!paymentsUnderBorder.Value)

Or you can distinctcount the values based on a condition at runtime in the report: =countdistinct(IIF(Fields!value.Value < 25, Fields!value.Value, Nothing)) This way you even can choose if you want to count the same offending value only one time or not > countdistinct vs count. 

The result: