Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a sample Data Set for Sales.
I have a few dimensions like Date, Category & SubCategory.
I am able to get the Highest Sale within my Straight Table.
I need to also add a column where I can show the customer who made the highest sale.
But my expression seem not working for the Top Customer.
Can someone give me a bit of help.
Sample attached.
Try this may be
When Dimension is Category
Aggr(If(Sales = Max(TOTAL <Category> Sales) and Aggr(Rank(If(Sales = Max(TOTAL <Category> Sales), Customer)), Category, Customer) = 1, Customer), Category, SubCategory, Order_date)
When Dimension is SubCategory
Aggr(If(Sales = Max(TOTAL <SubCategory> Sales) and Aggr(Rank(If(Sales = Max(TOTAL <SubCategory> Sales), Customer)), SubCategory, Customer) = 1, Customer), Category, SubCategory, Order_date)
What output are you hoping to see in the two tables that you have?
The customer names I expect with no dimension filters is as below.
Only Chloe, Young seems to be correct. Rest should be as the written text in Red.
I have this formula,
FirstSortedValue(DISTINCT Customer, Aggr(-Max(Sales), Category, SubCategory,Order_date))
Which looks promising.
There are two names for Category LW with the value 21
Try this may be
When Dimension is Category
Aggr(If(Sales = Max(TOTAL <Category> Sales) and Aggr(Rank(If(Sales = Max(TOTAL <Category> Sales), Customer)), Category, Customer) = 1, Customer), Category, SubCategory, Order_date)
When Dimension is SubCategory
Aggr(If(Sales = Max(TOTAL <SubCategory> Sales) and Aggr(Rank(If(Sales = Max(TOTAL <SubCategory> Sales), Customer)), SubCategory, Customer) = 1, Customer), Category, SubCategory, Order_date)