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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors