Sum totals from a table which needs to be filtered through two tables

[AaronO]

I have a power app which contains three layers of data.  Top layer is the Risk Card.  Second layer is Plant Info, including a reference to the parent Risk Card ID.  Third layer is a table of "Event" times and dollar values, which has a reference to the Plant Info ID.

How can I sum up the dollars in the Events table which belong to a specific Risk Card?  And once I accomplish that I will need to also filter by Events which share the same date to further group them.  I have been trying to use nested filter statements, but I can' figure out how to get the data to pass through.

Thanks!

[RandyHayes]

@AaronO 

You could certainly do it as additional columns to your data table.

Ex. 

AddColumns(
    Filter(RiskCard, RiskCardID = something) As _risk,
    "_eventSum", Sum(Filter(Events, RiskCardID = _risk.RiskCardID), dollarValue)
)

If that is the direction of the hierarchy of the data, then it would be best in that way.

If you want to do it from the Events up, then something like this:

AddColumns(
    GroupBy(Events, 
        "RiskCardID", "_data"
    ),
    "_eventSum", Sum(_data, dollarValue)
)

I hope this is helpful for you.

[AaronO]

Thank you for your reply.  I am not sure if this is exactly what I'm looking for.  The way you wrote the code it looks like both stages are referencing the same ID, but in my case Table1 is referenced by Table2 and Table2 is referenced by Table3.  I tried this with some modifications I thought would fix this, and it didn't produce the output I needed, which is a single number to place in a Label control.  Please help me understand what the parts of the code are doing.  I'm assuming the AddColumns function outputs a table which is the source table and the added column and can be placed inside another function as a data source.

Thanks!

[RandyHayes]

@AaronO 

I believe it was this statement : "How can I sum up the dollars in the Events table which belong to a specific Risk Card?" that threw me.  What it really should state (correct me if I'm wrong) is "How can I sum up the dollars in the Events table which belong to a specific Plant Info ID associated with a specific Risk Card?"

Let me know if that is correct before I offer up some solutions for you.  I'm at end of day here, so I will respond tomorrow.

[AaronO]

Yes, that would be an accurate summary. 

Thanks for all your help Randy. It’s very much appreciated!

[RandyHayes]

@AaronO 

Okay...you'll be my last post for the day 😁

You can nest the formulas as much as you need.. this is all the data shaping functionality you have in PowerApps!!

Here is an example:

AddColumns(
    ForAll(
        Filter(RiskCard, RiskCardID = something) As _risk,
        With({_plant: LookUp(PlantInfo, RiskCardID = _risk.ID)},

            Patch(_risk,
                {_plantInfo:, 
                    {_plant: _plant,
                     _events: Filter(Events, PlantInfoID=_plant.ID)
                    }
                }
            )
        )
    ),
    "_eventSum", Sum(_plantInfo._events, dollarValue)
)

This will filter the RiskCard table by whatever you want.  Then It will look up the PlantInfo (assuming there is only one record in PlantInfo for the RiskCard).

Then it will add the _plantInfo column to the Risk record.  That column is a record that contains the Plant record and the list of all the events for the Plant.

This is all in a ForAll table function that will return the table with that schema (your Risk record and and added column called _plantInfo).  ForAll is used over the AddColumns in this case only because we only wanted to look up the PlantInfo once for performance and we needed to use a With to do that...can't use a With in an AddColumns function.  So, ForAll is better.

Now, finally, the outer AddColumns will add a column to the table produced by the ForAll called _eventSum, which is just a Sum of the doallarValue column in the _plantInfo column record with the _events column (which is a table).

Hopefully that makes sense!  Adjust names and such as needed and give it a try.

I will swing back with you tomorrow to see how it went.

[AaronO]

There are multiple plantinfo records per risk card.

I'm also getting an error after this line at the comma:

{_plantInfo:,

 It says Unexpected Character.

Thanks for taking the extra time to explain each step in addition to posting the code.  I desire to learn so I don't have to ask so many questions and your post is helping me do that.

[RandyHayes]

@AaronO 

Oh you keep throwing some curves!! 😂

First, the comma was a typo!

So, your formula gets a little more complex with multiple plant records.

It would look more like this:

AddColumns(
    ForAll(
        Filter(RiskCard, RiskCardID = something) As _risk,
        With({_plants: Filter(PlantInfo, RiskCardID = _risk.ID)},

            Patch(_risk,
                {_plants: 
                    ForAll(_plants As _plant, 
                       {_plant: _plant,
                        _events: Filter(Events, PlantInfoID=_plant.ID)
                       }
                    )
                }
            )
        )
    ),
    "_eventSum", Sum(ForAll(_plants, Sum(_events, dollarValue)), Value)
)

This one will create a column called _plants that will contain a table of plants and the events for that plant.

Then the final AddColumn will Sum a Table (From the ForAll) that each record will be a single column called Value that will contain the Sum of the individual list of _events.

Let's see where that takes you.

[AaronO]

I am very bad at explaining my problem.  I think I have identified the miscommunication here this time.

I only need to find the result for the current Risk Card.  This is not being processed across the entire table, only viewed when the risk card is opened.

Let me try to explain it again and see if the code you posted matches.

I need to start with one Risk Card ID.  Then identify all plant-risks which have been created for this risk card and form some sort of collection (not necessarily a "collection" as defined in powerapps)  of those IDs.  Then with the collection of IDs of plant-risks, go through the events table and find all of the events which match any ID in our collection and a specified date and add them up.  Then with the summed events, display that on the screen.

I'm so sorry for the lack of clarity.

[AaronO · Accepted solution]

Somehow my other post regarding this was lost.  I found the solution to my problem with the helpful guidance of @RandyHayes .

Here's what I ultimately used to get the summarized data:

Text(Sum(
    ForAll(
        Filter(
            'Plant Risks',
            Value('Risk Card'.Value) = varCurrentRisk.ID
        ) As _Risk,
        Sum(
            Filter(
                'Plant Risk Events',
                'Plant Risk ID' = _Risk.ID,
                'Event Date' = EventID
            ),
            'Event Risk'
        )
    ),
    ThisRecord.Value
),"#,##0","de-DE") & " €"