Sum of multiple columns of an array

[Frog64]

Hi,

I have an array called "Lines" that looks like this:

I want to do the sum for each type.

Example: for A, 10+5+5=20. For B = 1, and for C = 7.

I tried but my loops are wrong.

Can anyone help me on this?

Thanks 🙂

[Expiscornovus]

Hi @Frog64,

You could use an increment variable action like in this thread suggested by @fchopo, https://powerusers.microsoft.com/t5/Building-Flows/Sum-an-array-column/m-p/794951

In your setup you first want to identify the unique Type values (A, B, C). When you have those in a collection you could loop through them and use a filter to find the values per type. With a nested apply to each you could increment the integers and calculate the sum in a temp variable.

Below is an example approach.

This first Select (the Select - Types) action uses the item()['Type'] expression in the Map field. I see I forgot to add that to the notes.

With your test data it would look something like this:

[Frog64]

Hi @Expiscornovus  and thanks for your answer.

I am a beginner and I don't know the Select - Value. Moreover, I don't know what you put in the Increment variable for TmpInteger in the Value field.

I tried to do the following flow in order to store the result in an array so I can use it later in my flow, but my loop is still bad so my total amount is false:

[Frog64]

@Expiscornovus I tried to do the same flow as you but I have an error when I want to increment the variable.

Do you know how to handle this?

[Expiscornovus]

Hi @Frog64,

Let me clarify a couple of things by sharing all the steps from my earlier example.

1. Add a Manually Trigger a flow action

2. Add a Initialize variable action. Use SumText as the Name. Select Type String. Leave the Value empty

3. Add a Initialize variable action. Use TempInteger as the Name. Select Type Integer. Leave the Value empty

4. Add a Initialize variable action. Use Lines as the Name. Select Type Array. I used this as the value

[
  {
    "Type": "A",
    "Value": 10
  },
  {
    "Type": "A",
    "Value": 5
  },
  {
    "Type": "B",
    "Value": 1
  },
  {
    "Type": "C",
    "Value": 2
  },
  {
    "Type": "C",
    "Value": 5
  },
  {
    "Type": "A",
    "Value": 5
  }
]

5. Add a Select Action. Rename the action from 'Select' to 'Select - Types'. Use the Lines variable in the From. Use the following expression in the Map field (first switch the Map field to text mode by click the button on the right side).

item()['Type']

6. Add Apply to each loop action. Rename the action from 'Apply to each' to 'Apply to each - Loop through unique type items'. In the Select an output from previous steps use the following expression:

intersection(body('Select_-_Types'),body('Select_-_Types'))

7. Within the Apply to each - Loop through unique type items action add a Compose action. Rename the action from 'Compose' to 'Compose - Type Item'. Use the following expression for the inputs.

item()

8. Within the Apply to each - Loop through unique type items action add a Filter Array action. Rename the action from 'Filter Array' to 'Filter array - Find Values for Type'. Use the Lines Variable in the From field. In the criteria use 

item()['Type'] is equal to @{outputs('Compose_-_Type_Item')}

9. Within the Apply to each - Loop through unique type items action add a Select Action. Rename the action from 'Select' to 'Select - Value'. Use the Body of the Filter Array action in the From. Use the following expression in the Map field (first switch the Map field to text mode by click the button on the right side).

item()['Value']

10. Within the Apply to each - Loop through unique type items action add a second Apply to each loop action. Rename the action from 'Apply to each' to 'Apply to each - Loop through value items'. In the Select an output from previous steps use the Output of the Select - Value action.

11. Within the Apply to each - Loop through value items action add an Increment Variable action. Select the TempInteger variable. In the value use the following expression:

item()

12. Within the Apply to each - Loop through unique type items action add an append to string variable action. Select the SumText variable. Use the following expression 

Total Sum for Type @{outputs('Compose_-_Type_Item')} is @{variables('TempInteger')}@{decodeUriComponent('%0A')}

13. Within the Apply to each - Loop through unique type items action add a set variable action. Select the TempInteger. (Re)Set the value to 0. 

14. Add a Compose action (outside the apply to each). Use the SumText variable for the Inputs.