Help to count "favorite" products

[larsquaade]

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

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(If(ProductID = FirstSortedValue(TOTAL <[CustomID]> [ProductID], -Aggr(Sum([SalesValue]), [CustomID], [ProductID]))), Sum(SalesValue)), [CustomID], [ProductID]))

[larsquaade]

Formula works - thank you.