2 Replies Latest reply: Jul 11, 2018 4:47 AM by Lars Quaade RSS

    Help to count "favorite" products

    Lars Quaade

      I have the following problem:

       

      Loaded Table_data:

      CustomerID

      ProductID

      OrderID   

      SalesValue

      Qty // quantity

      OrderDate


      I have created a Chart Table with:

       

      dimension: [CustomerID]

      measure: FIRSTSORTEDVALUE ( [ProductID] , -AGGR(SUM( [SalesValue]), [CustomID], [ProductID] )))

       

      This is a list of each customers "favorite" product (measured by SalesValue). Now I would like to add the following measure to the created Chart Table: The total sales value each customer has spend on his specific favorite product.

       

      I have tried

      SUM({<[ProductID]={"=FIRSTSORTEDVALUE ( [ProductID] , -AGGR(SUM( [SalesValue]), [CustomerID], [ProductID] ))"}>}[SalesValue])

      but this formula is incorrect and actually returns the customers total sales i.e. equivalent to SUM( [SalesValue] ).

       

      Any help welcome; how should the correct formula look like? and please help me understand why the above set-formula just returns SUM ([SalesValue]) ?

       

      Expanding on this, any suggestion on how to count the customers who has chosen [ProductID] as a favorite, i.e. a Chart Table with only the following

      dimension: [ProductID]

      measure: count the number of (distinct) customers who has chosen [ProductID] as a Favorite                  

       

      Regards

      Lars