Expression to return aggregation of a column value (e.g "Value") from another table for unique list of another column

Hi,

I have a google worksheet called Transactions… which has 3 columns:

User, Type, Value

I would like to write an expression to return summation of column “Value” for each unique value in column “Type”

For example… if my table has 10 entries

User Type Value

Alpha Type A 100
Alpha Type B 200
Alpha Type C 150
Beta Type A 100
Beta Type B 200
Beta Type C 150
Charlie Type A 100
Charlie Type B 100

Then my query should result in
Type Value
Type A 300
Type B 500
Type C 300

I hope someone can help me with this.

Hi @Ankit_Android,

You have mentioned another table in your subject line. I believe you may wish to give somewhat more details like below

Is it correct to assume , the example of the table you have given with 8 records is one table, called say UserType table and the table where the values for the type are aggregated with three rows is another table , called say AddValues

Is there any referencing between these two tables? For example is AddValues table referenced in UserType Table?

In general presuming the tables are not referenced to each other , the expression for aggregate value in of Value column ( a virtual column) in AddValues table could be something like
SUM(SELECT(UserType[Value],[TypeU]=[_THISROW].[TypeS]))

Here TypeU is type column name in user table and TypeS is type column name in AddValues table.

You may wish to update if the tables are referenced and if you would like value to be updated in a real column in AddValues table. Then there could be a different approach for aggregation.

@Suvrutt_Gurjar,

You are absolutely right. Let me explain it again.

Table 1: UserEntry has 5 columns:

Date, Year, User, Type, Value

Table 2: YearlySummary has 1 Column

Year

Now, I want to add a virtual column in Table 2 which will be of type “List” which should show unique list of entries from Table 1 for column “Type” and cumulative value of column “Value” for given Year and Type

I will create a view for Table 2:

which will show all the unique values of Year and a aggregated values of all the types for that Year.

Hope it helps.

Hi @Ankit_Android,

Thank you for more details. The table 2 does not seem to have a type column. Could you please mention how to you plan to have a yearly summary by type in the table 2? Will there be 3 summaries for each year by Type A ,B and C? In that case do you wish to have 3 VCs per record ( that is per year) in table 2?

Also could you please mention you wish to have list of values and not an aggregated value right?

Hi @Suvrutt_Gurjar,

Table 2 doesn’t have Type column and hence the idea is to bring unique list of values from column “Type” from Table 1 along with consolidated/aggregated values for each Type for that year in table 2 in VC column which will be of list type.

For e.g.
Table 1:
Date Year User Type Value

01-Sep-2018 2018 Alpha Type A 100
05-Sep-2018 2018 Alpha Type B 200
07-Sep-2018 2018 Alpha Type C 300
01-Sep-2018 2018 Beta Type A 100
05-Sep-2018 2018 Beta Type B 200
07-Sep-2018 2018 Beta Type C 300
01-Sep-2018 2018 Charlie Type A 100
05-Sep-2018 2018 Charlie Type B 200
07-Sep-2018 2018 Charlie Type C 300

Table 2:

Year
2018

I want to write an expression for a VC column in Table 2: which will give following results:

Type Value
Type A 300
Type B 600
Type C 900

Please note: the example given is a simpler form than the actual data sets which has multiple columns and rows. and hence I would be creating multiple VCs in table 2 which will fetch similar list for different columns for a given year. for e.g

User Values
Alpha 600
Beta 600
Charlie 600

I hope this provides a better view

If all you want are virtual columns in Table 2, just create the virtual columns with suitable app formulas, e.g.:

SUM(
  SELECT(
    Table 1[Value],
    AND(
      ("Type A" = [Type]),
      ([_THISROW].[Year] = [Year])
    )
  )
)





1 Like

I believe your formula will not result in unique values. Let me check

In addition, your formula is incorrect for my requirement. I want to fetch all the unique values of column “Type” and get corresponding aggregated values of column “Value” in Virtual column

My expression does that, if you’re using the term “virtual column” correctly.

1 Like