Forum Discussion

yamacha's avatar
yamacha
Icon for Helper I rankHelper I
2 years ago

Matrix calculation with multi-layered SUMX

Hi there I have been struggling with the  Matrix Calculation for years, and SUMX + VALUES seems to be most handy way that can cover most situations. However as you can  below it won't help when ther...
Need Help
yamacha's avatar
yamacha
Icon for Helper I rankHelper I
2 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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 Shen

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.