Adding totals of costs to a different column in the data model

[rrrggg20]

Hello all,

If I have project costs estimated by 2 people, how can I combine them to an existing data model field

data model looks similar to this (load Script, but can be changed)

Program, grouping

Program 1, Group1

Program 1, Group2

Program 1 = Function 1 = 2000

Program 1 = Function 2 = 3000

Program 1 = Function 3 = 4000

Group 1 should consist of Function 1 and Function 3

Group 2 should consist of Function 2

In the expressions, I can assign the cell to Function 1 + Function 3 using if's, sums,....

however I cannot associate the resulting value to the name "Group 1" or "Group 2"

so my result grid shows

Program 1  "-' $6000

[edwin · Accepted solution]

assuming you have two tables, one table has Group and Program and 2nd table has Program and Function and you want to aggregate them and add the aggregation to the 1st table:

table1:
load * inline [
Program, Group
Program 1, Group1
Program 1, Group2
];
NoConcatenate
table2:
load * inline [
Program, Function, Amount
Program 1,Function 1,2000
Program 1,Function 2,3000
Program 1,Function 3,4000
];

left join (table1) 
load Program, Group, sum(Amount) as Total
group by Program, Group;;
load Program, 
if(Function='Function 2','Group2','Group1') as Group,
Amount
resident table2;

[edwin]

so my result grid shows

Program 1  "-' $6000

didnt you mean Group1 has aggregated value of 6000 and not Program 1?

[rrrggg20]

I should have been more clear.  the resulting grid should show

Program 1      Group 1    $6000

Program 1      Group 2     $3000

so what happens is we get costs from lets say materials, labor as well as internal workers

so function 1 = "Material Cost"

Function 2 = "Internal Cost"

Function 3  = "Labor Cost"

in the summary report, I am trying to display the combination of Labor Cost and Material Cost as "External Cost"  this is a parameter which is created in the data model as an inline table

so we need to add Function 1 + Function 3 and display in the External Cost bucket.

I will check out your example now

Thanks 

[rrrggg20]

also "External Costs" and "Internal Costs" are 2 values under the Cost Account field (inline table)

These are not separate fields

[edwin]

the solution is the same you just have to change the field names and values

Group becomes your external and internal costs

[edwin]