Forum Discussion

etane's avatar
etane
Icon for Helper V rankHelper V
1 year ago
Solved

Incorrect Matrix Total when using MAXX Measure

Hello. I have a dataset where I am using a measure to calculate the latest inputed forecast by Rep, Product and Calendar Year/Month via a MAXX function a key date column. The calculation is cor...
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi etane 

    Could you please follow below steps: 

    1. Create a basic total (for reference)
    Sum of Amount = SUM ( Data[Amount] )

        2.Naïve “latest forecast” (shows the total bug)

    •  Fix: make the Total sum rows
    • Use ISINSCOPE to detect when you’re on a row vs. the total; at the total, sum each Rep’s row value.
    Forecast Selected (naive) =
VAR vLastDate =
   CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS ( Data[Date] ) )
RETURN
CALCULATE ( SUM ( Data[Amount] ), Data[Date] = vLastDate )
    • This works per Rep row.
    • At the Total, there’s no Rep in scope; it finds the overall max date (in the sample it’s 2025-04-01) and returns that single month’s amount (= 60,000).
    Forecast Selected =
IF (
   ISINSCOPE ( Data[Rep] ),
   [Forecast Selected (naive)],
   SUMX ( VALUES ( Data[Rep] ), [Forecast Selected (naive)] )
)

       3.Build the matrix

    • Rows: Data[Rep]
    • Values: Sum of Amount, Forecast Selected

     

rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

Hi etane 

Could you please follow below steps: 

  1. Create a basic total (for reference)
Sum of Amount = SUM ( Data[Amount] )

    2.Naïve “latest forecast” (shows the total bug)

  •  Fix: make the Total sum rows
  • Use ISINSCOPE to detect when you’re on a row vs. the total; at the total, sum each Rep’s row value.
Forecast Selected (naive) =
VAR vLastDate =
   CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS ( Data[Date] ) )
RETURN
CALCULATE ( SUM ( Data[Amount] ), Data[Date] = vLastDate )
  • This works per Rep row.
  • At the Total, there’s no Rep in scope; it finds the overall max date (in the sample it’s 2025-04-01) and returns that single month’s amount (= 60,000).
Forecast Selected =
IF (
   ISINSCOPE ( Data[Rep] ),
   [Forecast Selected (naive)],
   SUMX ( VALUES ( Data[Rep] ), [Forecast Selected (naive)] )
)

   3.Build the matrix

  • Rows: Data[Rep]
  • Values: Sum of Amount, Forecast Selected

 

  • etane's avatar
    etane
    Icon for Helper V rankHelper V
    1 year ago

    Thanks!

  • etane's avatar
    etane
    Icon for Helper V rankHelper V
    1 year ago

    rohy .  So, when one product is selected, the measure works because there's one max date per rep.  However, when the product is not selected, the measure doesn't work because the max date only works on the product that has the latest date.  

    Could you please modify the measure so there's a max date not only by rep but also by product?