Skip to Content
1
Feb 02, 2023 at 12:27 PM

Table footer Calculation footer based on the value from other colum (using where and In clauses)

156 Views Last edit Feb 02, 2023 at 12:43 PM 7 rev

Hi,

I need to display some calculation result based in the footer on my table. The calculation result is based on the value of others column. I'm lost with the formula to use and I don't find the correct solution.

My report is displaying contracts defining with a Budget (Planned Amount). Each contract is linked to a Project Id (where a budget is defined: "Budget" column). And several contracts (CPM Id) can be linked to the same Project Id.

There my data:

image001.png

The footer calculation allows to do the sum of the Project Budget and other calculations.

The Calculation of the values "(Planned) Procurement Procedures" (A) and "(Planned) Specific Contracts" (B) are correctly defined (they do the sum of the Planned Amount depending on the value of the column "Procurement Contract Type").

I have an issue with the two last calculation footer rows:

(Planned) % (E) is correctly implemented but depends on the Total IM Budget calculation (D) which is wrong:

The value for the Total IM Budget calculation (D) should be the sum of the Project Budget (Budget value column) for all cpm Id not cancelled (Phase name column). And it should takes into account this rule: if a project is the same for several Contracts, the budget should be counted once.For instance, for the contracts (CPM Id 2337, 2396 and 2400, the project ID is 6634 and the budget 750,000.00 should counted only once into.

I wrote this formula but it's not correct because it's not taken into account the phase:

=Sum(Max([Project Id Budget]) In ([Project Id]))

using the clause "In" for avoiding to duplicated the value and after filtering it's counting the value only once:

But without Filtering (1st picture) I got €1,380,455.55 and I should get €1,030,455.55. (there the Contracts Id 2346 linked to the project 8491with budget = 350,000.00 should not be counted as it's cancelled).

I tried this formula but it's not working:

=Sum(Max([Project Id Budget]) In ([Project Id]))Where([Phase Name]<>"Cancelled")

Could you help me please for implemented properly the formula ?

Thanks in advance for your help

Attachments

image001.png (71.1 kB)