# Top Customer for each Product

Dear DNA Team,

I will appreciate your explanation regarding one issue that occurred to me but I manage to resolve it.

Data Model:

The above screenshot shows the formula with a slightly different pattern.
I was trying to calculate top customer name for each job category and then the revenue that the customer generated.

In the Pattern 1.

Top Customer Name = MAXX(
TOPN(1,SUMMARIZE(Customer,Customer[Customer Name],“GP”,[P&L GP]),
[GP],DESC),
Customer[Customer Name])

Top Customer Name GP = MAXX(
TOPN(1,SUMMARIZE(Customer,Customer[Customer Name],“GP”,[P&L GP]),
[GP],DESC),
[GP])

But I run into an issue as the above formula brought the customer name for the job category that sales were never generated.

In order to fix this, I wrapped up the formula in a simple if statement:

Top Customer Name v3 = IF(ISBLANK([P&L GP]),BLANK(), MAXX(
TOPN(1,SUMMARIZE(Customer,Customer[Customer Name],“GP”,[P&L GP]),
[GP],DESC),
Customer[Customer Name]))

But then once again I run into an issue

The formula brought incorrect customer name and the sales actually are negative.

To fix it, i replaced customer table with the data table

Top Customer Name v2 = MAXX(
TOPN(1,SUMMARIZE(Data,Customer[Customer Name],“GP”,[P&L GP]),
[GP],DESC),
Customer[Customer Name])

As per the below screen, the results are correct. My question is why the first two formulas didn’t work correctly?

Thank you,

Matty

@Matty Because Job Category in the Matrix can Filter

SUMMARIZE(Data,Customer[Customer Name],“GP”,[P&L GP])

but not
SUMMARIZE(Customer,Customer[Customer Name],“GP”,[P&L GP])

as the filter from Jobs Category can’t travel to Customer table.

@AntrikshSharma Thank you for your wisdom, It makes sense!

Regarding the below lecture where Sam used exactly the same formula referencing the Customer table, it didn’t give any blank values as Sam had perfect Data

Thank you,

Matty

@Matty here are some optimized versions:

``````Top Customer Name v2 =
IF (
ISINSCOPE ( 'Job category'[Job category description] ),
MAXX (
TOPN (
1,
SUMMARIZE (
Data,
Customer[Customer Name]
),
"@GP", [P&L GP]
),
[@GP], DESC
),
Customer[Customer Name]
)
)
``````

.

``````Top Customer Name v3 =
IF (
ISINSCOPE ( 'Job category'[Job category description] ),
CONCATENATEX (
GENERATE (
VALUES ( 'Job category'[Job category description] ),
TOPN (
1,
CALCULATETABLE (
VALUES ( Customer[Customer Name] ),
CALCULATETABLE ( Data )
-- CROSSFILTER ( Data[Customer], Customer[Code], Both )
),
[P&L GP], DESC
)
),
Customer[Customer Name],
", "
)
)``````
6 Likes

Thank you @AntrikshSharma!

Never heard of inscope formula but just watching it on yt