aggregation problem

[#1]

Hi all

I want to concatenate 2 table for 2 business units and create a dashboard using that info,but my issue is that they calculations are different for 1 measure.

e.g

Business Unit A

Sale = sum(sale)/Total (sale)

Business Unit B

Sale = avg(Sale)

I tried to sum( (sum(sale)/Total (sale)) + avg(sale)) --------it does not work

or avg( (sum(sale)/Total (sale)) + avg(sale)) -----also does not work

How can I go about doing that so that I can have 1 chart/pivot table  for both BUs

Thanking you in advance

[MK_QSL]

Can you provide sample data along with your expected result?

[#3]

Business Unit A

Product	City	User	Sales
Cheese	Alonso	Ben	23
Butter	Vryburg	Jon	12
Milk	Florida	Peter	23
Bread	Florida	Brian	66
Rice	Alonso	Ben	23
Butter	Orlando	Jon	12
Milk	Florida	Peter	23
Bread	Florida	Brian	66

Calculation = avg(Sales) --- lets assume you have a user as a dimesion

Business Unit B

Product	City	User	Amount	Trans_Type
Rice	Alonso	Ben	23	Sale
Samp	Vryburg	Jon	12	Order
Milk	Florida	Peter	23	Order
Bread	Florida	Brian	66	Return
Rice	Alonso	Ben	23	Sale
Samp	Orlando	Jon	12	Sale
Milk	Florida	Peter	23	Order
Bread	Florida	Brian	66	Order

Calc = (sum({<Trans_Type={'Sale'}>}Sale)/sum(Sale)

now I want an average of sale for both BUs on 1 chart/pivot table

[MK_QSL]

Sorry but still having one confusion, What is the difference of Amount for BU A and BU B?

[Anonymous]

You need to add "source" columns to the concatenated  tables that will point what BU you use in the formula.

And expression might be:

if (Source = 'A')

     sum(sale)/Total (sale)

else avg(Sale)

[Anonymous]

you need additional one attribute in the table with BU information (A,B). based on this field you can use it in the expression.