Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Automate - Using Flows
Unanswered

Sum - Greater than 0 and Less than 0

(1) ShareShare
ReportReport
Posted on by 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)
  • Chriddle Profile Picture
    7,907 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
    Great catch, Tomac!
  • Tomac Profile Picture
    278 Super User 2025 Season 1 on at
    Sum - Greater than 0 and Less than 0
    In one of your expressions, you're using a capital letter i instead of a lower case letter L:
    xpath(xml(addProperty(	json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/I0)')
     
    Should be:
    xpath(xml(addProperty(	json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/l0)')
     
    For the less than 0 figures.
     
    If this fixes the last remaining issue, please mark Chriddle's post as the answer as he provided these expressions.
  • Chriddle Profile Picture
    7,907 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
    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
    7,907 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
    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
    7,907 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
    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
    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.
  • Chriddle Profile Picture
    7,907 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)
     
     
     

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

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

Featured topics

Restore a deleted flow