Forum Discussion

StephenClarke's avatar
StephenClarke
Frequent Visitor
5 years ago
Solved

Return highest sales value Product per Client

Dear Community,

 

I have a large table of sales transaction datafor all our cleints and all the respective products of ours they have purchased over time.

I would like some DAX to add a column and tag each client with a prefered product which i have defined as the product they have spent the most on over their lifetime with the company.

 

ClientProductValueDESIRED NEW COLUMN
Prefered Product
111A5A
112B5B
111A5A
112B5B
111B5A
112A5B

 

I have attempted the following:

 

Top Programme =
VAR ClientID = Booking[ClientID]
VAR ProductID =
SUMMARIZE(FILTER(Booking, Booking[ClientID] = ClientID),
Booking[Brochure_ID],
"ProdCount", SUM(Booking[Total_Headline_Value])
)
RETURN
SELECTCOLUMNS(
TOPN( 1, ProductID, [ProdCount]),
"Client ID", Booking[Brochure_ID]
)
 
But get the error:
A table of multiple values was supplied where a single value was expected.
 
In the case of ties i would be happy to go arbitarily with the first or last record. Whatever makes the code easier.
 
Help!
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    StephenClarke 

    You can add the following column to get the desired result. I found additional columns in your formula that was not in the sample data you provided, I hope you can adjust the following formula to suit your columns,

     

    Top Programme = 
var __Client = Booking[ClientID]
var ClientProductAmount = 
        TOPN(1,
            ADDCOLUMNS(
                SUMMARIZE(
                    FILTER(Booking, Booking[ClientID] = __Client),
                    Booking[Product]
                ),
                "Amount", CALCULATE(SUM(Booking[Value]))
            ),
            [Amount]
        )
return
    MAXX(ClientProductAmount,Booking[Product])

     

     

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    StephenClarke 

    You can add the following column to get the desired result. I found additional columns in your formula that was not in the sample data you provided, I hope you can adjust the following formula to suit your columns,

     

    Top Programme = 
var __Client = Booking[ClientID]
var ClientProductAmount = 
        TOPN(1,
            ADDCOLUMNS(
                SUMMARIZE(
                    FILTER(Booking, Booking[ClientID] = __Client),
                    Booking[Product]
                ),
                "Amount", CALCULATE(SUM(Booking[Value]))
            ),
            [Amount]
        )
return
    MAXX(ClientProductAmount,Booking[Product])