how to combine of 2 selected numbers column.

(0) Share

Report

Posted on by Wei Ming Leong

313

thank you for reading this post.

i have a lineitems list that contain that contain Cost (number), Currency (Choices SGD/YEN/USD/MYR), ConvertedSGDAmount (numbers)

each items can have different cost and currency.
when my finance colleague process the claims i have 1 fill for them to key in the exchange rate and this exchange rate will * the Cost and become ConvertedSGD Amount.

in the report screen, the RedBox Cost will sum all cost be it YEN currency or USD.
How do i make it only calculate only SGD Cost and ConvertedSGDAmount?

the current formula is

"SGD$" & If(Sum(Filter(LineItems,ReportID.Id = SelectedReport.ID),Cost)>0,Text(Sum(Filter(LineItems,ReportID.Id = SelectedReport.ID),Cost),"[$-en-US]#,###.00"),"0.00")

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WeiMingLeong,

Do you want to sum the cost only with SGD Cost?

I think you should filter the records based on the Currency value, if the Currency value is SGD, sum the Cost with SGD currency selected:

"SGD$" & If(Sum(Filter(LineItems,ReportID.Id = SelectedReport.ID),Cost)>0,Text(Sum(Filter(LineItems,ReportID.Id = SelectedReport.ID,Currency.Value="SDG"),Cost),"[$-en-US]#,###.00"),"0.00")

To sum the ConvertedSGDAmount, you just need to replace the Cost with the ConvertedSGDAmount column name.

Was this reply helpful? Yes No
Wei Ming Leong 313 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-qiaqi-msft ,
Thank you for your reply.
I want the report to show total amount of SGD and ConvertedSGD amount .
in the item screen my Finance colleague will key in the exchange rate and the convertedSGD amount will auto generate.

Then at the View report i want the ConvertedSGDrate and other items that is in SGD to sum together.
For example this 2 red box sum together to become SGD$230 in the green box.

Was this reply helpful? Yes No
Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WeiMingLeong,

Okay, I got this.

Currency(Choice type) Cost(Number type) ConvertedSGDrate(Number type) FinanceExchangeRate

SDG 100 130 1.3

SDG xxx 100 1.3

I thought you have a choice column named 'Currency' including SGD/YEN/USD/MYR for options, and you would sum only the SDG selected in your Currency for the ConvertedSGDrate column.

"SGD$" & Text(Sum(Filter(LineItems,ReportID.Id = SelectedReport.ID,Currency.Value="SDG"),Rating),"[$-en-US]#,###.00")

Check the above saying and tell me if this is what you want.

Was this reply helpful? Yes No
Wei Ming Leong 313 on at

Like
a
(0)

Report
Copy link

Link copied!

hi @v-qiaqi-msft ,

there is still the choice column for the different currency.
the formula you provided was able to work with what i want.
the total sum that i need is be 130+245.44+92.72
Currency(Choice type) Cost(Number type) ConvertedSGDrate(Number type) FinanceExchangeRate
SGD 130
Yen 25000 245.44 0.0098
MYR 300 92.72 0.3090

Was this reply helpful? Yes No