Return highest sales value Product per Client

[StephenClarke]

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.

Client	Product	Value	DESIRED NEW COLUMN Prefered Product
111	A	5	A
112	B	5	B
111	A	5	A
112	B	5	B
111	B	5	A
112	A	5	B

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!

[StephenClarke]

Genius! 

Thank you so much for the replies.

[Ashish_Mathur]

Hi,

Do you want a measure or a calculated column formula?

[Fowmy · Accepted solution]

@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])