cancel
Showing results for
Did you mean:
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.

 Id Type Sub-Type Property Val1 Val2 Val3 1 XYZ XYZ-Good A 12 32 11 1 XYZ XYZ-Good B 43 42 16 1 XYZ XYZ-Good C 38 28 47 1 XYZ XYZ-Good D 31 61 22

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:

 Property Val2-Val1 Val3-Val1 A 20 -1 B -1 -27 C -10 9 D 30 -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

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

Hi @akulrastogi :

Could you tell me:

Is this the result you want?

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(
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)``

Min

Best Regards,

Bof

2 REPLIES 2
Highlighted
Community Support

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

Hi @akulrastogi :

Could you tell me:

Is this the result you want?

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(
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)``

Min

Best Regards,

Bof

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.

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available 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

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

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)