cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

How to perform calculations using data from multiple rows?

Hello everyone! I am relatively new to PowerApp development. Please help me with the following.

I have my data in the below format. 

IdTypeSub-TypePropertyVal1Val2Val3
1XYZXYZ-GoodA123211
1XYZXYZ-GoodB434216
1XYZXYZ-GoodC382847
1XYZXYZ-GoodD316122

 

Here the Id, Type and Sub-Type columns will always have the same data in each row. Also, the user gets an option to edit the Val1, Val2 and Val3 here.

 

Once user edits the values, I need to make the below calculations:

PropertyVal2-Val1Val3-Val1
A20-1
B-1-27
C-109
D30-9

 

Now, I need to compare the Val2-Val1 values for all the properties and find out the suitable property. For instance, one scenario is where i need to find the lowest Val2-Val1 value between A, B and C. So, i would need two results i.e. Suitable Property = C, Property Value = -10

I am unable to iterate through the rows to make the comparisons. Any help would highly be appreciated.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to perform calculations using data from multiple rows?

Hi @akulrastogi :

Could you tell me:

Is this the result you want?

1.jpg

The point is using Min() funciton.I'v made a test for your reference.

My data source-TheTable:

ClearCollect(
    TheTable,
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"A",Val1:12,Val2:32,Val3:11},
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"B",Val1:43,Val2:42,Val3:16},
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"C",Val1:38,Val2:28,Val3:47},
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"D",Val1:31,Val2:61,Val3:22}
)

1\Add a table and set it's Items proeprty to:

ShowColumns(
    AddColumns(
        TheTable,
        "Val2-Val1",
        Val2 - Val1,
        "Val3-Val1",
        Val3 - Val1
    ),
    "Property",
    "Val2-Val1",
    "Val3-Val1"
)

2\Add a label and set it's Text proeprty to:

"Suitable Property = "&LookUp(TheTable,Val2-Val1=Min(TheTable,Val2-Val1)).Property

3\Add a label and set it's Text property to:

"Suitable Property = "&(LookUp(TheTable,Val2-Val1=Min(TheTable,Val2-Val1)).Val2-LookUp(TheTable,Val2-Val1=Min(TheTable,Val2-Val1)).Val1)

In addition,I think these links will help you a lot:

AddColumns

Min

Best Regards,

Bof

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: How to perform calculations using data from multiple rows?

Hi @akulrastogi :

Could you tell me:

Is this the result you want?

1.jpg

The point is using Min() funciton.I'v made a test for your reference.

My data source-TheTable:

ClearCollect(
    TheTable,
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"A",Val1:12,Val2:32,Val3:11},
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"B",Val1:43,Val2:42,Val3:16},
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"C",Val1:38,Val2:28,Val3:47},
{Id:1,Type:"XYZ",'Sub-Type':"XYZ-Good",Property:"D",Val1:31,Val2:61,Val3:22}
)

1\Add a table and set it's Items proeprty to:

ShowColumns(
    AddColumns(
        TheTable,
        "Val2-Val1",
        Val2 - Val1,
        "Val3-Val1",
        Val3 - Val1
    ),
    "Property",
    "Val2-Val1",
    "Val3-Val1"
)

2\Add a label and set it's Text proeprty to:

"Suitable Property = "&LookUp(TheTable,Val2-Val1=Min(TheTable,Val2-Val1)).Property

3\Add a label and set it's Text property to:

"Suitable Property = "&(LookUp(TheTable,Val2-Val1=Min(TheTable,Val2-Val1)).Val2-LookUp(TheTable,Val2-Val1=Min(TheTable,Val2-Val1)).Val1)

In addition,I think these links will help you a lot:

AddColumns

Min

Best Regards,

Bof

View solution in original post

Highlighted
Regular Visitor

Re: How to perform calculations using data from multiple rows?

@v-bofeng-msft Thanks for the guidance. I was able to achieve what I wanted. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,321)