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...
  • Fowmy's avatar
    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])