Getting the Max Value of Each Row Item (Summing the Duplicates too)

[0XMaijeh]

Hi Guys,

I have this problem where I want to return the max value for each row item, and where there are duplicates I want to sum them. 

This is a sample of the table I am working with; 

And this is what I want to achieve in the end;

Looking forward to the solutions!!!!!

[ryan_mayu · Accepted solution]

@0XMaijeh 

maybe you can try this

Table = 
var tbl=SUMMARIZE('Table (2)','Table (2)'[bottle type],'Table (2)'[SHIPING CODE],'Table (2)'[WAREHOUSE],'Table (2)'[DISTRBUTOR],"value",sum('Table (2)'[STOCK VALUE]))
var tbl2=ADDCOLUMNS(tbl,"rank",rankx(FILTER( tbl,[bottle type]=EARLIER('Table (2)'[bottle type])),[value],,DESC))
var tbl3= FILTER(tbl2,[rank]=1)
return SELECTCOLUMNS(tbl3,"bottle type",[bottle type],"shipping code",[SHIPING CODE],"warehouse",[WAREHOUSE],"distributor",[DISTRBUTOR],"value",[value])

pls see the attachment below

[v-yalanwu-msft · Accepted solution]

Hi, @0XMaijeh ;

You could create measures as follow:

Measure = 
CALCULATE(SUM('Table'[STOCK VALUE]),ALLEXCEPT('Table','Table'[bottle type],'Table'[SHIPING CODE]))

STOCK VALUE2 = 
VAR _MAX=
MAXX(SUMMARIZE(ALLEXCEPT('Table','Table'[bottle type]),[SHIPING CODE],"1",[Measure]),[1])
RETURN IF(_MAX=[Measure],_MAX)

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@0XMaijeh 

maybe you can try this

Table = 
var tbl=SUMMARIZE('Table (2)','Table (2)'[bottle type],'Table (2)'[SHIPING CODE],'Table (2)'[WAREHOUSE],'Table (2)'[DISTRBUTOR],"value",sum('Table (2)'[STOCK VALUE]))
var tbl2=ADDCOLUMNS(tbl,"rank",rankx(FILTER( tbl,[bottle type]=EARLIER('Table (2)'[bottle type])),[value],,DESC))
var tbl3= FILTER(tbl2,[rank]=1)
return SELECTCOLUMNS(tbl3,"bottle type",[bottle type],"shipping code",[SHIPING CODE],"warehouse",[WAREHOUSE],"distributor",[DISTRBUTOR],"value",[value])

pls see the attachment below

[0XMaijeh]

Thank you so much! Your solution worked well for the context provided. However I just noticed that when I tried to filter by weeks, it does not change (I forgot to add weeks to the sample table I provided yikes!). Is there any way out of this?

[ryan_mayu]

if you want to apply filters, I think you need to create a measure, pls try @v-yalanwu-msft 's solution.