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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi All,

Firstly  ahadkarimi thank you for your solution!
And yamacha to my understanding,you want to get the error for each product line for that month and then add that up, right? 
The matrix can be aggregated for you, you don't need to write your own aggregation function, maybe my data is too simple to reproduce your problem, if you can can you upload some sample data, maybe it will help to solve your problem.

 

ERROR_LP = ABS(SUM('basic data'[Actual Qty])-SUM('basic data'[FCST Qty]))

 

I would be extremely proud if I could solve your problem and look forward to hearing from you!

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.

 

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

    Hi Xingshen

    Thanks for the comments, but unfortunately it's not that simple. There is a further layer below the product line that is the UPN (SKU), and I need to calculate the error on these 2 different levels (that's why you see I have to columns for Error). 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

       

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