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?



        • 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.



            • 3. Re: Aggregate of an aggregate


              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

                       "Commission" is your commission measure

                       "Product" is your attribute column


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


              Mark right if helpful


              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.




                • 5. Re: Aggregate of an aggregate
                  Thomas Dodds

                  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.