Sum a Max of customer sales (Monthly Max by year) summed by a region

[lsullivan6311]

Here is an example of the data:

Midwest Region  YTD 2022 Max = 4500 + 500 = 5,000

Customer         Region         Year        Month          SalesVol

       A               Midwest       2021           1                   400

       A               Midwest       2021           2                  1500

       A               Midwest       2021           3                  4500

       B               Midwest       2021           1                    150

       B               Midwest       2021           2                   500

       B               Midwest       2021           3                    30

[v-shex-msft · Accepted solution]

HI @lsullivan6311,

It seems like a common multiple aggregation requirement. For this scenario, you can check the below measure formula if it helps: (my expression use summarize function to group records by Customer, Region, Year, Month fields and use the iterator function sumx to apply the second level aggregate)

formula =
SUMX (
    SUMMARIZE (
        Table,
        [Customer],
        [Region],
        [Year],
        [Month],
        "MaxSales", MAX ( Table[SalesVol] )
    ),
    [MaxSales]
)

Regards,

Xiaoxin Sheng

[lsullivan6311]

This does not sum the max sales values; it still just gives me the Max Customer sales volume withing that Region.  I get the same number using this calculation:  

 SalesMax = MAXX (Table, [SalesVol])