cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Summing the Maximum by Category within Month

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

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

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
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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