Get Max of a column grouping by another columns

[sar7aan]

I have a table similar to this:

all what I want is to add a new measure to a table in power bi that returns the maximum "Value" grouped by "Project" and "Category" columns 

[v-xiaosun-msft · Accepted solution]

Hi @sar7aan 

I suggest that you can use CALCULATE function and EARLIER function to create a calculated column, then you will get the expected result.

Sample data:

Create a column.

Column =
CALCULATE (
    MAX ( 'table1'[VALUE] ),
    FILTER (
        'table1',
        'table1'[Project] = EARLIER ( 'table1'[Project] )
            && 'table1'[Category] = EARLIER ( 'table1'[Category] )
    )
)

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@sar7aan , please try this measure:

MaxValueByProjectAndCategory = 
    CALCULATE(
        MAX('Table1'[Value])
        ,ALL('Table1'[Date])
    )

This yeilds these results:

[sar7aan]

Thanks EylesIT,
But the table above is just for example. 
Say that I have a table of 20 columns and I want to get the max of one column grouping by another two columns ...it won't be practical to exclude all the other 17 columns .... is there another way?

[FreemanZ]

In such case, typically a table would be created with the code below:

 

MaxTable =

ADDCOLUMNS(

    SUMMARIZE (Data, Data[Project], Data[Category]),

    "MaxValue",

    CALCULATE (MAX(Data[Value]))

)

With SUMMARIZE, we could include and summarize columns as many as needed. Columns from other table could also be included, as long as they are from a table on the one side of the relationship with Data table. 

[Anonymous]

@sar7aan , Could you please post a sample data table with the 20 columns and what the expected output should be, please?

When you say "it won't be practical to exclude all the other 17 columns" do you mean that you want to display all 20 columns in the table visual, but you still want this measure to only group by the Product and Category per row? If that is the case, then try this version of the measure:

MaxValueByProjectAndCategory = 
    VAR vProject = SELECTEDVALUE('Table1'[Project])
    VAR vCategory = SELECTEDVALUE('Table1'[Category])
    RETURN
    CALCULATE(
        MAX('Table1'[Value])
        ,ALL('Table1')
        ,'Table1'[Project] = vProject
        ,'Table1'[Category] = vCategory
    )