Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
gmatt98
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 NameAccount/Month KeySurvey DateBrandItems
Account 1Account1_20231111/6/2023Brand A

25

Account 1Account1_20231111/6/2023Brand B

5

Account 1Account1_20231111/9/2023Brand B

10

Account 1Account1_20231111/9/2023Brand C

25

Account 1Account1_20231111/6/2023Brand 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 NameAccount/Month KeyBrandItems (Correct)Items from 11/6 "used" in next table to calc the account level measure (incorrectly)
Account 1Account1_202311Brand A

25

25

Account 1Account1_202311Brand B

10

5

Account 1Account1_202311Brand C

25

0

Account 1Account1_202311Brand D

25

25

incorrect at account level:

Account NameAccount/Month KeyIntended Result:Incorrect Result:
Account 1Account1_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
v-zhangti
Community Support
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])

vzhangti_0-1700725435574.png

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.

View solution in original post

4 REPLIES 4
v-zhangti
Community Support
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])

vzhangti_0-1700725435574.png

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

DivisionRegionAccount
Division 1Region AAccount 1
Division 1Region AAccount 2
Division 1Region BAccount 3
Division 1Region BAccount 4
Division 2Region CAccount 5
Division 2Region CAccount 6
Jihwan_Kim
Super User
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

 

Jihwan_Kim_0-1700539463228.png

 

Jihwan_Kim_1-1700539927430.png

 

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.


Go to My LinkedIn Page


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:

DivisionRegionAccount
Division 1Region AAccount 1
Division 1Region AAccount 2
Division 1Region BAccount 3
Division 1Region BAccount 4
Division 2Region CAccount 5
Division 2Region CAccount 6

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors