Sum Values in Matrix based in another value in that matrix

Hey MRoth ,

despite my last questions from a little earlier, I think this measure is doing the trick regarding the Forcasted Value:

tom forecasted value = 
// offset value
var currentOffset = SELECTEDVALUE( 'Offset'[Parameter] )
return
CALCULATE(
    sum( 'Forecasts'[Forecasted Value] ),
    OFFSET(
        CurrentOffset * -1,
        SUMMARIZE(ALLSELECTED('Forecasts'), 'Dim Forecasts'[ForecastVersion], 'Forecasts'[ForcastWeek]),
        ORDERBY('Forecasts'[ForcastWeek]),
        KEEP,
        PARTITIONBY('Dim Forecasts'[ForecastVersion])
    )
)

Please be aware that I changed the data type of Forecasts[ForecastsWeek] to "whole number"; otherwise, the ORDERBY clause in the OFFSET function will order the weeks alphabetically.

At least the results look reasonable 😉

Hopefully, this helps to tackle your challenge.

Regards,
Tom

Hi MRoth,

Could you please try with below DAX function , if it is not works could you please provide some sample data of .pbix file so that I can try to provide solution.

Forecasted Values by Selected Date =
Var _SelectedDate = SELECTEDVALUE ( 'FactTable'[Forecast creation Date] )
RETURN
CALCULATE(SUM('FactTable'[Forecasted Value]),
    FILTER(ALL('FactTable'), 'FactTable'[Forecast creation Date] = _SelectedDate)
)

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

Hey MRoth ,

please prepare a pbix file that contains sample data but still reflects the semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link.

I'm wondering why you mentioned the Matrix visual, as the results look like a "normal" table.

As a measure returns a scalar value, there needs to be something that provides a context for the visualization, otherwise there is only one row.

Maybe you need to rethink your current solution in a way that you have the column "Forecast Creation Date" in your calendar table, this column then provides the context.

Then the measure is using a SUMX to iterated across the facttable.

Regards,

Tom

Hi MRoth 
I wanted to check if you had the opportunity to review the information provided by TomMartens . Please feel free to contact us if you have any further questions.

Thank you.