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:





      Qty // quantity


      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