6 Replies Latest reply on Jun 15, 2015 1:53 PM by SriniVEERAVALLI

# Aggregate of an aggregate

Hi All,

I have a need to show a number that calculates a bit weird... I have to calculate the (sum(Sold) * avg(Commission) both by product), and them SUM it all into one line.

How can I achieve this?

Thanks,
Alejandro

• ###### 1. Re: Aggregate of an aggregate

create two different measure column sum(Sold),avg(Commission) at product level then try this  SUM(sum(Sold) * avg(Commission)).

• ###### 2. Re: Aggregate of an aggregate

Hi, I tried that, but I still get one row per product. I don't know how to make it so it only shows me one row, since I don't want to see the Products, I don't want to have the product column in the report at all.

Thanks,
Alejandro

• ###### 3. Re: Aggregate of an aggregate

Hi,

You can try this:

1 - Use your two measures without any advanced config (sum by..., use only sum aggregate on RPD);

2 - In the report, drag-and-drop any column and then edit it formula as bellow:

sum("Sold" by "Product") * avg("Commission" by "Product")

where "Sold" is your sold measure

Ensure your column aggregate is set to SUM (into column formula) and DON´T use your column Product in this report

Felipe Idalgo

• ###### 4. Re: Aggregate of an aggregate

Hi, thanks for the replies, but I still can't get it to work.

I tried that, but I am still getting the same result as doing the simple (Sold * Commission).

The problem is that they need first to compute each value by Product Name, and then sum all the (Sold * Commission) by each product. That is the total they need. In one row, without the Product names in the report.

Thanks,
Alejandro

• ###### 5. Re: Aggregate of an aggregate

Perhaps you need to store the extended commission at the product level in the warehouse first...  start by using a view (mat. View or view either will do the trick) that contains your unique keys for product and sale and the fact of sum(sales) * avg(commission).  Expose that in your physical layer, build a physical and logical star around it ... now you only have to worry about the aggregation of SUM on the product of that new fact column.

• ###### 6. Re: Aggregate of an aggregate

check this

sum(Sold by product) * avg(Commission by product)

in this way try for other options to calculate on fly.