The problem is that your Calculation is using the INDEX and ROW functions. Typically, you don't want INDEX in a SUM because INDEX specifically tells it to only retrieve a single value and unless you're adding up column values in a single row, that's not what you want.

What is happening is that your second table is 1 row, so that ROW function returns the first index, and when that is applied to the other table column you only get the first value.

INDEX and ROW are primarily used when you want to reference something from another column within the same table and row.

For example, if you wanted to do Column A + Column B in Column C, you would use index and row on columns A and B so C would only use the values for its own row.

However, in your case, you want the entire sum from the other table so index isn't the way to go, but then your problem is that you have to exclude the "hidden" ones.

There are ways to do that using COUNTIF, however, that's not really the "ideal" solution here.

The real problem with that is that you're using two separate columns for Cost to School when in reality they are part of the same thing and you just need the value to change.

What you should do there instead is use a formula/calculation in your Cost to School column.

For example,

=IF(INDEX(Table.UpgradeColumn,ROW())="Yes",1870.23,0)

Then instead of having two separate columns and having to exclude the "hidden" ones, the value of the Cost column would just change dynamically.

Once you have your source rows calculating those values correctly, you can just sum them up in the second table with a simple function that doesn't require filtering or added complexity.

=SUM(Table.Cost_to_School_6)

There's a lot of other stuff you can do depending on if you need that dropdown to actually store a Yes/No value, for example, you could just make the dropdown values the numeric values and do a sum of that column instead, however, that will impact other things like gateway routing and report values so it's not always the right solution.