Is it possible to do a calculation of several records in another record within an AddColumns?

[MasterRA]

Yes, I know, the question sounds crazy but I don't know how else to explain it; the context is as follows, I am trying to make an indicator of the number of product records between a line and a state ("Line" is an example) where each line has several states but I need that indicator to attach the total number of records per line without count the states, the number of by-products and the total of products and by-products; These are two independent tables and I want everything to be in a single "query".

For practical purposes, I will only exemplify only two states, "in progress" and "finished" and for lines I will leave "assembly" and "packaging".

AddColumns(
	LineTable,
	"states", AddColumns(
		Table(
			{ state: 1, name: "In progress" },
			{ state: 2, name: "Finished" },
			{ state: 97, name: "FirstTotal" },
			{ state: 98, name: "By-products" },
			{ state: 99, name: "GeneralTotal" }
		) As States,
		"total", Switch(state,
		98, CountRows(
			ByProductsTable,
			state in [1, 2]
		),
		99, 0, // Here should go the sum of those two records
		CountRows(
			ProductsTable,
			If(state <> 97,
				state = States.state,
				state in [1, 2]
			)
		)
	)
)

The question is, how to make the states 97 and 98 add up inside the AddColumns of the "States" column of the lines, in other words, that this sum remains in the state 99.

I found a solution but it is not what I wanted and it is to create redundancy with the total of states per line plus the number of by-products but I don't think this is efficient.

AddColumns(
	LineTable,
	"states", AddColumns(
		Table(
			{ state: 1, name: "In progress" },
			{ state: 2, name: "Finished" },
			{ state: 97, name: "FirstTotal" },
			{ state: 98, name: "By-products" },
			{ state: 99, name: "GeneralTotal" }
		) As States,
		"total", Switch(state,
		98, CountRows(
			ByProductsTable,
			state in [1, 2]
		),
		// ineffective solution (?)
		99, CountRows(
			ProductsTable,
			state in [1, 2]
		) + CountRows(
			ByProductsTable,
			state in [1, 2]
		),
		// ------
		CountRows(
			ProductsTable,
			If(state <> 97,
				state = States.state,
				state in [1, 2]
			)
		)
	)
)

The other possibility is to make the internal gallery, in the label, show the sum of those two records using the LookUp function and calling the corresponding records to make that sum, but I feel that what I propose can be done but it does not occur to me how to do it.

[Anonymous]

Hello!

An interesting challenge, however, there are some concerns when trying to do this in PowerApps. First and most important, delegation, depending on your data source and size of your data source, the "CountRows" may not be delegated 

Understand delegation in a canvas app - Power Apps | Microsoft Learn

As a recommendation I would use Power BI to do those aggregations (or even in the backend if you are using something like SQL DB) and then embed that in Power Apps if needed.

Sorry this is not a solution, but it is how I would tackle the challenge 

[MasterRA]

Yes, I know about delegation and the problem of using non-delegable functions, but for practical purposes and client requirements, the number of records of some tables is needed, and well, the most effective solution was to make those queries directly from the PowerApp; I solved this by loading a cache populating collections so that all the records can be obtained and counted, for the moment it works but the number of records is increasing every day, not in exorbitant amounts but if the number of records increases, I hope so at some point they exceed a considerable number, I will do it in the back-end from SQL but it would be necessary to restructure the PowerApp again for this; but let's suppose that the registers are stable and the CountRows works with the data that is in the collections, will there be an efficient method for what I mentioned in my original question?

[Anonymous]

Assuming there are no issues with delegation....

In that case a better way to do it is using GroupBy functionality along with aggregations. Here is a well-documented example.

GroupBy and Ungroup functions in Power Apps - Power Platform | Microsoft Learn

If you could share a sample of your tables, maybe I could give you some ideas

[MasterRA]

I understand the meaning of your answer but I feel that the GroupBy refers to already defined data, so what I need is inside an AddColumns and a data table is generated for each line, although with respect to data with which they appear in queries and make up any would be effective for testing; Now, I thought that with the PowerApps SUM function I was going to solve this, but I don't know if you can reference the internal table by line to only filter the records you want and add them accordingly; the only thing missing is that, how to know if the table under construction can be referenced with some entered data and from that data, add its content to another record.