Summing the Maximum by Category within Month

[gmatt98]

I am trying to return the number of items in each month and am having trouble returning the intended result. If there are multiple surveys in one month, I want to find the maximum items of each brand in that month. Using the example below, for Brand 2, my maximum should be 10 (from the survey taken on 11/9/2023). I also need the measures to sum correctly to an account level -- for the example below, I want to get 85 items (11/6 survey for Brand A, 11/9 survey for Brand B, 11/9 survey for Brand C, 11/6 survey for Brand D).

I know WHY it's not returning the right result, which I'll try to describe below, but can't get around how to fix the DAX to make it work correctly! 🙂

I am working with a table built out the following way:

Account Name	Account/Month Key	Survey Date	Brand	Items
Account 1	Account1_202311	11/6/2023	Brand A	25
Account 1	Account1_202311	11/6/2023	Brand B	5
Account 1	Account1_202311	11/9/2023	Brand B	10
Account 1	Account1_202311	11/9/2023	Brand C	25
Account 1	Account1_202311	11/6/2023	Brand D	25

Despite working correctly when compiling to an account/brand level, I get the following (incorrect) total results when looking at the complete Account 1 level because the meaures only look at the 11/6 surveys as a WHOLE when finding the total (so incorrectly taking the 11/6 survey for Brand A, 11/6 survey for Brand B, and 11/6 survey for Brand D, or 25+5+25):

working correctly at account and brand level -- 

Account Name	Account/Month Key	Brand	Items (Correct)	Items from 11/6 "used" in next table to calc the account level measure (incorrectly)
Account 1	Account1_202311	Brand A	25	25
Account 1	Account1_202311	Brand B	10	5
Account 1	Account1_202311	Brand C	25	0
Account 1	Account1_202311	Brand D	25	25

incorrect at account level:

Account Name	Account/Month Key	Intended Result:	Incorrect Result:
Account 1	Account1_202311	85	55

My measure is as follows:

Total Items = 

SUMX (
    VALUES ( Table1[Account/Month Key] ),
    MAXX(
        VALUES(Table1[Survey Date]),
            SUM(Table1[Items])) )
)

I know the Table1[Survey Date] column is causing the sumx iteration to happen over survey date 11/6 vs. 11/9, and finds that the total of 11/6 surveys (55) is greater than the total of 11/9 surveys (35), and thus returns 55 as the total. I can't figure out how to get the sumx iteration to ignore the previously designated survey date maxiumum!

It is probably a silly error that I am making, but I am stumped 🙂 Any help is greatly appreciated!!

[v-zhangti · Accepted solution]

Hi, @gmatt98 

You can try the following methods.
Measure:

Maxitem = 
CALCULATE(MAX('Table'[Items]),ALLEXCEPT('Table','Table'[Brand],'Table'[Account/Month Key],'Table'[Region],'Table'[Division]))

Result = 
Var _table=SUMMARIZE('Table','Table'[Division],'Table'[Region],'Table'[Account/Month Key],'Table'[Brand],"Maxitem",[Maxitem])
Return
SUMX(_table,[Maxitem])

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[gmatt98]

This answer works perfectly for my question as I've written it, so thank you! 

However, when I try to roll up the measure into higher levels of the account hierarchy, it no longer correctly totals the number of items. How can I adapt your measure to account for the needs of this roll-up?

Assume I need to be able to roll up to Region & Division using the following (example) account hierarchy architecture:

Division	Region	Account
Division 1	Region A	Account 1
Division 1	Region A	Account 2
Division 1	Region B	Account 3
Division 1	Region B	Account 4
Division 2	Region C	Account 5
Division 2	Region C	Account 6

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

INDEX function (DAX) - DAX | Microsoft Learn

Expected result measure: =
SUMX (
    VALUES ( Brand[Brand] ),
    CALCULATE (
        SUM ( Data[Items] ),
        INDEX (
            1,
            SUMMARIZE (
                Data,
                'Calendar'[Year-Month sort],
                'Calendar'[Date],
                Data[Account Name],
                Data[Account/Month Key]
            ),
            ORDERBY ( CALCULATE ( SUM ( Data[Items] ) ), DESC ),
            ,
            PARTITIONBY ( 'Calendar'[Year-Month sort] )
        )
    )
)

[gmatt98]

This answer works perfectly for my question as I've written it, so thank you! 

However, when I try to roll up the measure into higher levels of the account hierarchy, it no longer correctly totals the number of items. How can I adapt your measure to account for the needs of this roll-up?

Assume I need to be able to roll up to Region & Division using the following (example) account hierarchy architecture:

Division	Region	Account
Division 1	Region A	Account 1
Division 1	Region A	Account 2
Division 1	Region B	Account 3
Division 1	Region B	Account 4
Division 2	Region C	Account 5
Division 2	Region C	Account 6