Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BeginnerBI
Helper I
Helper I

count greater than 1000 (report builder)

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 

 

 

 

 

1 ACCEPTED SOLUTION
PaginatedDino
Helper I
Helper I

@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:

PaginatedDino_0-1681193955635.png

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: 

PaginatedDino_1-1681194299039.png

 

View solution in original post

1 REPLY 1
PaginatedDino
Helper I
Helper I

@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:

PaginatedDino_0-1681193955635.png

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: 

PaginatedDino_1-1681194299039.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors