Forum Discussion

MRoth's avatar
MRoth
Icon for Helper I rankHelper I
10 months ago
Solved

Sum Values in Matrix based in another value in that matrix

Hi,  I am pretty stuck on a issue where I got almost there but can't get the last bit to work. In Excel I'd use a Sumifs and be done in 2minutes.    Scenario is Forecast Data similar to this.  Fa...
Need Help
  • TomMartens's avatar
    TomMartens
    9 months ago

    Hey MRoth ,

    have fun.

    I will no longer work on this, because I consider the provided information lacking, and for this reason, I'm wasting my precious spare time.

    Regards,
    Tom

Anonymous's avatar
Anonymous
Not applicable
9 months ago

Hi MRoth ,

You can achieve your expected summarized output by creating a DAX table or measure that groups the data by Forecast Version and aggregates the corresponding Forecasted and Actual values. Try using the SUMMARIZECOLUMNS or SUMMARIZE functions in DAX to build a summary table that calculates totals for each version and then derives the Variance as the difference between the Actual and Forecasted values. For example, you can create a new calculated table that summarizes your base data by Forecast Version, sums the forecast and actual columns, and adds a calculated variance column. This approach will give you a clean, version-level comparison like the output you shared, with one row per forecast version showing the aggregated forecast, actual, and variance.

 

I hope this information helps. Please do let us know if you have any further queries.
Thank you

MRoth's avatar
MRoth
Icon for Helper I rankHelper I
9 months ago

Hi Anonymous , 
thanks for your reply. 

I cannot use a calculated table as this wouldn't update based on the slicer setting. 

I have tried to store a table virtually, and while I was able to sucessfully display the expected result,

 

unfortunately when I use sumx over this table and attempt to apply filtercontext to the versions via a visual, I don't get the expected results, presumably as I broke datalineage. I have been trying to work with treatas, but so far unsuccessful. 

 

Virtual Table

Define

VAR _Offset = 4

VAR _newtableVersion = 
    ADDCOLUMNS (
        VALUES ( Forecasts[ForecastedWeek] ),
        "UsedForecastVersion",
            CALCULATE (
                MAXX ( Forecasts, Forecasts[ForecastVersion] ),
                Forecasts[Relative Creation Week] <= _Offset *-1
            )
            )
            
VAR _Lookup = ADDCOLUMNS(_newtableVersion,
                "ForecastValue",LOOKUPVALUE(Forecasts[Forecasted Value],Forecasts[ForecastVersion],[UsedForecastVersion],Forecasts[ForecastedWeek],Forecasts[ForecastedWeek])
)

EVALUATE
_Lookup

Result of virtual Table


Sumx Measure

TEST Forecast by Version = 


VAR _Offset = 'Offset'[Parameter Value]

VAR _newtableVersion = 
    ADDCOLUMNS (
        VALUES ( Forecasts[ForecastedWeek] ),
        "UsedForecastVersion",
            CALCULATE (
                MAXX ( Forecasts, Forecasts[ForecastVersion] ),
                Forecasts[Relative Creation Week] <= _Offset *-1
            )
            )
            
VAR _Lookup = ADDCOLUMNS(_newtableVersion,
                "ForecastValue",LOOKUPVALUE(Forecasts[Forecasted Value],Forecasts[ForecastVersion],[UsedForecastVersion],Forecasts[ForecastedWeek],Forecasts[ForecastedWeek])
)

VAR _Sumx = CALCULATE(SUMX(_Lookup,[ForecastValue]),Forecasts[ForecastVersion] = SELECTEDVALUE(Forecasts[ForecastVersion]))

RETURN
 _Sumx


Unexpected Result