Top Customer for each Product

DAX DAX Calculations
1

Dear DNA Team,

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

Data Model:

image
image795×709 18.8 KB

image
image1288×733 66.3 KB

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.

image
image1266×680 21.2 KB

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

image
image1227×605 33.2 KB

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?

image
image1333×706 35.8 KB

Please find the attached pbi fileTask2.pbix (923.2 KB)

I will appreciate your feedback.

Thank you,

Matty

2

@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.

3

@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 :smiley:

Thank you,

Matty

4

@Matty here are some optimized versions:

Top Customer Name v2 =
IF (
    ISINSCOPE ( 'Job category'[Job category description] ),
    MAXX (
        TOPN (
            1,
            ADDCOLUMNS (
                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
5

Thank you @AntrikshSharma!

Never heard of inscope formula but just watching it on yt :slight_smile: