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

    Aggregate of an aggregate

    2970027

      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
          Raja_gopal

          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
            2970027

            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
              Felipe_Idalgo

              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

                       "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
                2970027

                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
                  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
                    SriniVEERAVALLI

                    check this

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

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