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
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!!
Solved! Go to 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.
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.
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 |
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.
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 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |