19 Replies Latest reply: Mar 18, 2014 10:31 AM by Manish Kachhia

# SUM TOTAL and AVG

Dear all,

I need a solution for my problem. I want to use the totals of the tables below for further calculations.

There must be a solution, but I couldn't find it. I tried AGGR, sum(total <field>), but without result.

Order 960 has 5 codes of 30 and order 961 has 1 code of 30.

I need the following figures:

- Total Orderbedrag = 180

- Avg Orderbedrag = 90

- Avg Bonbedrag = 30

Can you help me with this?

Kind regards,

Erwin

• ###### Re: SUM TOTAL and AVG

Can you share a sample app with small data?

• ###### Re: SUM TOTAL and AVG

Hi Manish and Shyamal,

This comes from my DB, so I do not have sample data. Or can I export this to excel?

kind regards,

Erwin

• ###### Re: SUM TOTAL and AVG

Just give us few lines in excel...

• ###### Re: SUM TOTAL and AVG

Manish Kachhia schreef:

Just give us few lines in excel...

I created a few lines in excel, just like you asked...

This should be enough for the problem, right?

Kind regards, Erwin

• ###### Re: SUM TOTAL and AVG

Hi All,

I will add two xls files. Kind regards, Erwin

• ###### Re: SUM TOTAL and AVG

SUM([Order Amount])

SUM(TOTAL [Order Amount])/COUNT(TOTAL DISTINCT OrderId)

or

SUM([Order Amount])/COUNT(DISTINCT OrderId)

Avg Bonbedrag = 30

• ###### Re: SUM TOTAL and AVG

Hi Manish,

This does not seem to work.

below the results of your expressions:

• ###### Re: SUM TOTAL and AVG

What is the dimension you are using?

• ###### Re: SUM TOTAL and AVG

It doens't matter if I use orderId or codeId.

the sum total stays the same.

• ###### Re: SUM TOTAL and AVG

On what basis both tables are linked with each other?

• ###### Re: SUM TOTAL and AVG

I've attached screenshot

• ###### Re: SUM TOTAL and AVG

But you haven't provided proper sample data files !

• ###### Re: SUM TOTAL and AVG

I don't find any common field in your excel files which can link these two tables...

• ###### Re: SUM TOTAL and AVG

Hi Erwin,

I have gone through the thread but it is not clear what exactly your issue is.

You have to create dummy data or export properly with out put you need.

as you mentioned "Order 960 has 5 codes of 30 and order 961 has 1 code of 30." it seems you can create this dummy data in Excel with 3 tabs as tables with output you needed.

cheers!!!

• ###### Re: SUM TOTAL and AVG

Like this?

• ###### Re: SUM TOTAL and AVG

Sorry I cannot open this document, because I am on personal license...

• ###### Re: SUM TOTAL and AVG

Create a Straight Table

Dimension = Orderid

Expressions

SUM(Amount)

SUM([Order Amount])

SUM(TOTAL<OrderId> [Order Amount])

SUM(TOTAL <CodeId,OrderId>[Order Amount])/COUNT(OrderId)

COUNT(CodeId)

AVG(Amount)