Forum Discussion

PDRTXRA's avatar
PDRTXRA
Helper I
3 years ago
Solved

Finding Best Product per Client

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.

ClientIDNameLastName
1JohnJohnson
2MarySmith
3PeterMiller

 

TransactionIDClientIDProductNameSalesValue
12Shirt5
21Sweater15
33Shirt5
43Pants10
52Socks1
61Boots20
71Shirt5
82Boots20
92Sweater15

 

Thank you in advance. 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      PDRTXRA
      Helper I
      3 years ago

      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.

  • LQuedas's avatar
    LQuedas
    Resolver II
    3 years ago

    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
  • PDRTXRA's avatar
    PDRTXRA
    Helper I
    3 years ago

    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.