Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Using Flows / Sum - Greater than 0 a...
Power Automate - Using Flows
Unanswered

Sum - Greater than 0 and Less than 0

(1) ShareShare
ReportReport
Posted on by Prem4253 487
Hello,
 
I'm using below flow, which filters Current Month and displays the overall sum from Spend Amount column
 
In Get Items filter query below expression is used:
 
(CompletionDate ge '@{utcNow('yyyy-MM-01T00:00:00')}') and (CompletionDate lt '@{addToTime(utcNow('yyyy-MM-01T00:00:00'), 1, 'Month', 'yyyy-MM-ddT00:00:00')}')
 
 
For a Post message in Teams, I've used below expression for MTD Count:
length(body('Select'))
And below expression for MTD Greater than 0 Spend
formatnumber(xpath(xml(outputs('JSON')), 'sum(/root/Numbers)'), 'C2')
The flow that I have, gives me overall spend, whereas I'm looking to have 2 different rows i.e. one for Greater than 0 and one for less than 0 (in below format):
 
But the MTD count should show overall count of current month. For e.g. in above screen shot:
Total Count = 47
Greater than 0 = $720,999.44
Less than 0 = ($567,45.22)
Categories:
Business Process Flows
  • Chriddle Profile Picture
    Chriddle 7,900 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
    concat(
	formatNumber(
		div(
			720999.4413000001,
			1000000
		),
		 'C2', 'en-us'
	),
	'M'
)
     
  • Prem4253 Profile Picture
    Prem4253 487 on at
    Sum - Greater than 0 and Less than 0
    Yes, it was giving incorrect output in Compose.
    Later, I recreated both the steps provided by you and it is working now.
     
    I modified below code to covert the currency value into millions:
     
    formatNumber( div (
    xpath(
	    xml(
		addProperty(
			json('{}'),
			'Root',
			addProperty(
				json('{}'),
				'Item',
				body('GreaternLess')
			)
		)
	),
	'sum(//Item/g0)'
), 1000000), 'C2')
     Values returning as below;
    $0.72
    ($0.03)
     
    Can you please suggest, how can we add M at the end of the above 2 values. Because we want users to read it as 0.72 million
  • Chriddle Profile Picture
    Chriddle 7,900 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
    I don't get it 😉
     
    ... is giving the correct output in Compose Action and In "Post message in a chat or channel"
    Now were the value is less than zero, it is displaying 0
    There is the correct output in a Compose, but it doesn't work?
    This requires further explanation.
  • Prem4253 Profile Picture
    Prem4253 487 on at
    Sum - Greater than 0 and Less than 0
     
    All of a sudden, flow has started acting strange. It was / is giving the correct output in Compose Action and In "Post message in a chat or channel"
     
     
    Now were the value is less than zero, it is displaying 0
     
  • Chriddle Profile Picture
    Chriddle 7,900 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
    formatNumber(
	div(
		720999.4413000001,
		1000000
	),
	 'C2', 'en-us'
)
  • Prem4253 Profile Picture
    Prem4253 487 on at
    Sum - Greater than 0 and Less than 0
     
    I figured out a small error in my expressions set in Compose Action. Select2 had a space i.e. Select 2, than I changed this as below:
     
    xpath(xml(addProperty(	json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select2')))), 'sum(//Item/g0)')
    And In "Post message in a chat or channel" I again added the expression as below, same as in Compose Action that is added
     
    xpath(xml(addProperty(	json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/g0)')
xpath(xml(addProperty(	json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/I0)')
    And below is the correct output.
     
    Not sure if I followed the right approach on adding the same expressions both in Compose as well Post message in a chat
     
    Now can you please advise, on how do we convert that values to show in $ Currency with 2 decimals. Basically dividing it by million values, to get the output as below:
     $      0.72
     $    (0.03)
     
  • Prem4253 Profile Picture
    Prem4253 487 on at
    Sum - Greater than 0 and Less than 0
     
    Below is the flow that is been created but I'm not able to get the desired output (see attached excel for your reference)
     
     
     
    field_13 is my Spend Amount column and field_7 is my Completion Date.
     
    The output should be as below in the Teams chat bot:
     
    Total Completion Date Count: 69
    Greater than 0 720999.44
    Less than 0 -32400.99
     
    Could you please advise further if any additional step or missing step I have missed.
    Greater and Less ...
  • Chriddle Profile Picture
    Chriddle 7,900 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
    Sorry, the expressions in my Map l0 and g0 are a bit too much ;)
    Instead of this if-equals nonsense, simply use the following:
     
    Map l0:
    min(item(), 0)
     
    Map g0:
    max(item(), 0)
     
     
     
  • Chriddle Profile Picture
    Chriddle 7,900 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
     
    Select
     
    Map l0:
    if(
	greater(
		item(),
		0
	),
	0,
	item()
)
    Map g0:
    if(
	less(
		item(),
		0
	),
	0,
	item()
)
     
    Compose 2
     
    Greater:
    xpath(
	xml(
		addProperty(
			json('{}'),
			'Root',
			addProperty(
				json('{}'),
				'Item',
				body('Select')
			)
		)
	),
	'sum(//Item/g0)'
)
     
    Less:
    xpath(
	xml(
		addProperty(
			json('{}'),
			'Root',
			addProperty(
				json('{}'),
				'Item',
				body('Select')
			)
		)
	),
	'sum(//Item/l0)'
)
  • Tomac Profile Picture
    Tomac 274 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
    In between your Select and your JSON actions, use two Filter Array actions: 1 to filter on Spend Amount is greater than or equal to zero, and the second to filter on Spent Amount is less than zero.
     
    Use the outputs of these two Filter Array actions to calculate each spend.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 1

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 1

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

Restore a deleted flow

Product updates

Microsoft Power Platform Community release plans