cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lewkir
Resolver I
Resolver I

AddColumn to a table which is sum of columns in a different table which match a reference in the first column

I have two tables, a list of courses and a list of people who have booked on to those courses and I am trying to add a column to the first table which will be a sum of the items in the second who match a reference, taken away from how many spaces are available on that course.

 

EG Table1
Ref | Course | Spaces

01 | Excel     | 10

02 | Word    | 10

03 | Office    | 5

 

Table 2

Name | Ref

Jon     | 01

Jane   | 01

Mark  | 02

 

So now I want to add a column "RemainingSpaces"
New Table:

Ref | Course | Spaces | RemainingSpaces

01 | Excel     | 10         | 8

02 | Word    | 10         | 9

03 | Office    | 5          | 5

 

I tried
AddColumns(Table1,"RemainingSpaces",Sum(Spaces,-CountIf(Table2, Ref = Ref)))

and it just seems to sum the whole second table.

 

Using [@Ref] just doens't work. I can't figure out how to get the calculation to use the reference from the list item it is in.

 

Any help much appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions

 

1.PNG

Both - Unbooked and 'List of Internal Course Applicants' have CourseCode column ? (In your example  was Ref and CourseCode)

If not, please use in formula above the name of the column from Unbooked on one side and in the other side the name of the column 'List of Internal Course Applicants'.

If so, please use:'

     AddColumns(

                       Unbooked,"RemainingSpaces",

                       Sum(

                             Places,

                            -CountIf(

                                      RenameColumns('List of Internal Course Applicants',

                                                                  "CourseCode",

                                                                   "Code"),

                           CourseCode=Code)

                          )

 )

View solution in original post

8 REPLIES 8
gabibalaban
Dual Super User
Dual Super User

Hi @Lewkir 

Can you try:

 

AddColumns(Table1,"RemainingSpaces",Sum(Spaces,-CountIf(Table2, Ref = Course)))

 

Because the column from table2 is Course (right ?)

 

Hope it helps !

Oh sorry my example is wrong, the columns have the same name in my actual data and it still doesn't work.

 

I'll edit the question.

Can you show me the error please ?

 

There isn't an error per se it just counts every item in the list instead of filtering it.

 

TAble issue.png

 

the totals are after the title name, you can see it is taking away the total number of (unfiltered) applicants from that item's spaces.

I think that your problem came from Places. I'm affraid that this field is recognized as Text (and considered 0 inside the Sum function), not as Number. 

 

Please try to change formula using Value(Places) instead of Places.

No it recognises the number of places as you can see by the difference in totals:

The places value for Accountability is 0 so it is taking the 89 total applicants from that to get -89 and for Coaching... it is 12 so it is taking 89 from that to get -79.

 

The issue is that it isn't filtering the Count by the coursecode (reference) and taking the number of applicants from each course away.

 

There is only 1 person signed up for 00081 so the value there should be 11 and 0 for the other course so that should remain 0.

 

1.PNG

Both - Unbooked and 'List of Internal Course Applicants' have CourseCode column ? (In your example  was Ref and CourseCode)

If not, please use in formula above the name of the column from Unbooked on one side and in the other side the name of the column 'List of Internal Course Applicants'.

If so, please use:'

     AddColumns(

                       Unbooked,"RemainingSpaces",

                       Sum(

                             Places,

                            -CountIf(

                                      RenameColumns('List of Internal Course Applicants',

                                                                  "CourseCode",

                                                                   "Code"),

                           CourseCode=Code)

                          )

 )

Yeah they both have a coursecode column.

 

Ah that worked, thank you.

 

Kind of annoying there's no built in way to select a column from a particular table if they have the same name.

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,658)