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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Kudoed Authors