Forum Discussion
Helper IMatrix calculation with multi-layered SUMX
Hi yamacha ,
I apologise for not providing you with a suitable solution as I didn't understand you correctly.
If sumx and value may not be able to calculate the value of the difference between the strata correctly, you can use the alexecpt function to add a qualification to determine the stratum you need to calculate
-- ERROR_LP_PL5 =
CALCULATE(
MAXX(
VALUES('Table'[ProductLine5]),
ABS(SUM('Table'[Actual Qty]) - SUM('Table'[FCST Qty]))
),
ALLEXCEPT('Table', 'Table'[LP_ABBR], 'Table'[MonthDate], 'Table'[ProductLine5])
)
-- ERROR_LP_UPN =
CALCULATE(
MAXX(
VALUES('Table'[UPN]),
ABS(SUM('Table'[Actual Qty]) - SUM('Table'[FCST Qty]))
),
ALLEXCEPT('Table', 'Table'[LP_ABBR], 'Table'[MonthDate], 'Table'[ProductLine5],'Table'[UPN])
)
If in doubt, check out the pbix file I uploaded and I'd be honoured if I could help you out!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Helper IHi Xingshen
Thanks for the warm follow-up. I tried with your codes, and found the issue below
As you were using the naming 'Table' to represent multiple tables, I'm kinda confused on the syntax below. I assume the 3 Tables with column quoted refer to the master tables, then what is the first 'Table'? I do have 2 tables for actual and forecasted demand respectively. Thanks.
ALLEXCEPT('Table', 'Table'[LP_ABBR], 'Table'[MonthDate], 'Table'[ProductLine5])
Hi yamacha ,
The ALLEXCEPT function is used in order to better determine the level at which you need to perform the subtraction to ensure that the correct subtraction is performed. If the ALLEXCEPT function is not included, it is possible that the two measurements will yield the same result according to the context of SUMX, which is in a nutshell to determine the level at which the subtraction should be performed to the Row of the matrix, and the level at which the subtraction should be performed. ALLEXCEPT is used to make a judgement on the Row, depending on what is put in the Row, and is not directly related to the different tables.
I would be honoured if my answer solves your query!Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi yamacha ,
Has your problem been solved after all this time, or has a new problem arisen, if there are any other questions on this issue, feel free to contact me and I'll get back to you as soon as I receive the message.
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- yamacha
Hi Xingshen
Sorry for the late response, busy indeed these days.
I kinda locate the root cause (not the aggregation forumula issue): once I apply the 'RemoveEmpty' function that is simply to scope out lines with empty Actual Qty/FCST Qty, the total will become weird (2nd pic below). Just wonder if you have any clue on it? Thanks!