Help calculating Max volume; Grand Totals don't match

[AACP4]

Hi Everyone, 

Hope you can help me with this problem I´ve been working on for a couple of days without success. I have made a dummy-table to represent the data. I have these fields Customer, Volume, Direction, Corridor, Date and Port. I want to get the maximum Volume per Year, month and Customer. For example if Customer1 had a volume in January of 100,125, 200 and 150 in years 2020, 2021, 2022 and 2023, respectively, the final value should be 200 and so on for the rest of the months and the grand total should be the sum of those max values

I have used a quick measure to calculate the max values per year, then I use in my visualization the other fields to see how it should look with those constraints, but when I download the data, the aggregations seem off and the values are not the same as in PowerBI. 

I couldn't attach the files as I'm a new user:

https://drive.google.com/drive/folders/1uSsfUd1d1Ow-RbZ2GYR3tJC-j7ipwRdv?usp=sharing 

Thanks!!

[lbendlin]

Always, always have a Calendar table in your data model.

[AACP4]

lbenlin thank you for your advice and solution, it's almost what I wanted, I'd like the total "Max Vol" to be the sum of the Monthly "Max Vol" values.

In this example instead of getting 27667 at max volume, it should be the sum of the monthly results (30756)

[lbendlin · Accepted solution]

Volume max across years =
SUMX (
    ADDCOLUMNS (
        VALUES ( 'Calendar'[Month] ),
        "MY",
            MAXX (
                ADDCOLUMNS (
                    ALLSELECTED ( 'Calendar'[Year] ),
                    "s", CALCULATE ( SUM ( Sheet1[Volume] ) )
                ),
                [s]
            )
    ),
    [MY]
)