Customer with Highest Sale in Straight Table

[Chellappan-Pillai]

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.

[sunny_talwar]

What output are you hoping to see in the two tables that you have?

[Chellappan-Pillai]

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.

[sunny_talwar]

There are two names for Category LW with the value 21

[Chellappan-Pillai]

I will take the first alphabetically occurring value , if there are multiple.

[sunny_talwar · Accepted solution]

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)