Incorrect Matrix Total when using MAXX Measure

[etane]

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 correct inside the matrix, but the total is wrong.  

For example, the total latest forecast should be 210,000 but it's showing 60,000:

Please help edit the Forecast Selected measure so that the total shows up correctly in the total.  

My test file is linked here:  Link

Thanks!

PS.  This same measure works with the original dataset.  The dataset in the linked file is a summarized version of the original data table with validfromdate filtered and some irrelevant columns removed.  Logically, this shouldn't have an ill affect on the matrix total.  If anyone can explain why I am getting the wrong totals with this dataset, I'd appreciate it.

[techies]

Hi @etane please try this

Forecast Selected n =

SUMX(

    SUMMARIZE(Data, Data[Rep], Data[Product]),

    VAR MaxDateForRep =

        CALCULATE(

            MAX(Data[ValidFromDate]),

            ALLEXCEPT(Data, Data[Rep])

        )

    RETURN

        CALCULATE(

            [Forecast],

            Data[ValidFromDate] = MaxDateForRep

        )

)

[etane]

Hello @techies .

I pasted the measure into the test app.  

I find the measure is not picking up every rep's forecast such as Customer 004:

[rohit1991 · Accepted solution]

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]

@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?

[etane]

Thanks!