Finding Best Product per Client

[PDRTXRA]

Hello.

I have two tables. One with clients. One with sales.

I want to create 3 columns in my clients table.

First column: Name of the product in which they spent the most money.

Second column: Name of the product in second place.

Third column: Name of the product in third place.

ClientID	Name	LastName
1	John	Johnson
2	Mary	Smith
3	Peter	Miller

TransactionID	ClientID	ProductName	SalesValue
1	2	Shirt	5
2	1	Sweater	15
3	3	Shirt	5
4	3	Pants	10
5	2	Socks	1
6	1	Boots	20
7	1	Shirt	5
8	2	Boots	20
9	2	Sweater	15

Thank you in advance. 

[PDRTXRA]

Actually, this is finding the product where the client has the transaction with the most value.

What I want is to find the product where the client has spent more money. Meaning, I can have a client that bought 1 Boots for 20 but bough 10 Shirts for 5. Meaning he spent 20 on Boots and 50 on Shirts. In that case, with this Solution, I'm getting Boots where I should get Shirts. And just to finish, I would need to filter the sales table at the same time.

[LQuedas]

Hey Guys,

just to give a complementary approach, you can use VARIABLES instead EARLIER function as suggested by DAX GUIDE , what makes the code more readable

First column =

VAR _ADDCOLUMN =

    ADDCOLUMNS (

        Sales,

        "Rank",          

            VAR CurrentClientID = Sales[ClientID]            

            RETURN RANKX (

                FILTER ( Sales, Sales[ClientID] = CurrentClientID),

                Sales[SalesValue],

                ,

                DESC,

                DENSE

            )

    )

RETURN

    MAXX (

        FILTER (

            _ADDCOLUMN,

            VAR CurrentClientID =  Client[ClientID]

            RETURN AND ( [ClientID] = CurrentClientID, [Rank] = 1 )

        ),

        [ProductName]

    )

Cheers, LQ

[Anonymous · Accepted solution]

Hi @PDRTXRA ,

You can try code as below to create calculated columns in 'Client' table. 

First column = 
VAR _ADDCOLUMN =
    ADDCOLUMNS (
        Sales,
        "Rank",
            RANKX (
                FILTER ( Sales, Sales[ClientID] = EARLIER ( Sales[ClientID] ) ),
                Sales[SalesValue],
                ,
                DESC,
                DENSE
            )
    )
RETURN
    MAXX (
        FILTER (
            _ADDCOLUMN,
            AND ( [ClientID] = EARLIER ( Client[ClientID] ), [Rank] = 1 )
        ),
        [ProductName]
    )

Second column = 
VAR _ADDCOLUMN =
    ADDCOLUMNS (
        Sales,
        "Rank",
            RANKX (
                FILTER ( Sales, Sales[ClientID] = EARLIER ( Sales[ClientID] ) ),
                Sales[SalesValue],
                ,
                DESC,
                DENSE
            )
    )
RETURN
    MAXX (
        FILTER (
            _ADDCOLUMN,
            AND ( [ClientID] = EARLIER ( Client[ClientID] ), [Rank] = 2 )
        ),
        [ProductName]
    )

Third column = 
VAR _ADDCOLUMN =
    ADDCOLUMNS (
        Sales,
        "Rank",
            RANKX (
                FILTER ( Sales, Sales[ClientID] = EARLIER ( Sales[ClientID] ) ),
                Sales[SalesValue],
                ,
                DESC,
                DENSE
            )
    )
RETURN
    MAXX (
        FILTER (
            _ADDCOLUMN,
            AND ( [ClientID] = EARLIER ( Client[ClientID] ), [Rank] = 3 )
        ),
        [ProductName]
    )

Result is as below.

Here I create a virtual table in my code, if you don't want to add a rank column in 'Sales' table, you can use the workaround above. Or you can add a rank column firstly in Sales table and then column will be easier.

Rank =
RANKX (
    FILTER ( Sales, Sales[ClientID] = EARLIER ( Sales[ClientID] ) ),
    Sales[SalesValue],
    ,
    DESC,
    DENSE
)

First column 1 =
CALCULATE (
    MAX ( Sales[ProductName] ),
    FILTER (
        Sales,
        AND ( [ClientID] = EARLIER ( Client[ClientID] ), Sales[Rank1] = 1 )
    )
)

Second column and Third column are in the same logic.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PDRTXRA]

What if I need to add a filter to my Sales table? 

Meaning I only want it to consider certain category of products.

Thank you in advance.