topic Re: Summing the Maximum by Category within Month in DAX Commands and Tips

Summing the Maximum by Category within Month

gmatt98 — Mon, 20 Nov 2023 21:45:50 GMT

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!!

Re: Summing the Maximum by Category within Month

Jihwan_Kim — Tue, 21 Nov 2023 04:13:35 GMT

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] ) ) ) )

Re: Summing the Maximum by Category within Month

gmatt98 — Tue, 21 Nov 2023 15:44:54 GMT

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

Re: Summing the Maximum by Category within Month

gmatt98 — Tue, 21 Nov 2023 15:51:11 GMT

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

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

Re: Summing the Maximum by Category within Month

v-zhangti — Thu, 23 Nov 2023 07:44:39 GMT

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.