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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.