Dynamic Max

[Alan_]

Hi, I need to find the max values based a column value, basicly the highest value for each site.

So for site 1 = 180, site 2 = 173 and site 3 = 130. The data below is a sample, the actual table runs to 1000's of rows taking the site number over 1500. 

I am going to use this within a further calacultion so either a column or measurt would likely do.  I have tried various MAX functions but cant work out the expresision to filter to the site numbers. Thanks, 

SITE_NO	Number	RPT_MONTH
1	127	01/07/2017 00:00
1	76	01/06/2017 00:00
1	180	01/05/2017 00:00
1	135	01/04/2017 00:00
2	173	01/07/2017 00:00
2	132	01/06/2017 00:00
2	96	01/05/2017 00:00
2	68	01/04/2017 00:00
3	72	01/07/2017 00:00
3	79	01/06/2017 00:00
3	107	01/05/2017 00:00
3	130	01/04/2017 00:00

[Alan_]

Both answers work perfectly - thanks! I hadn't considered a calculated summary table - this seems like the cleanest solution thank you very much. 

[Anonymous · Accepted solution]

You can create a calculated table:
Table2 = SUMMARIZE(Table1;Table1[SITE_NO];"Max Site Value";max(Table1[Number]))

Table1Table2

[Phil_Seamark]

Hi @Alan_

Here is a calculated column that might work.

Max Number for Site = 
VAR SiteCol = 'Table1'[SITE_NO]
RETURN CALCULATE(
                MAX('Table1'[Number]),
                FILTER(
                    ALL('Table1'),
                    'Table1'[SITE_NO] = SiteCol
                    )
                   )